# Posts by pangolin

• ## choose the customer in drop down menu the customer address was auto genrate

Re: choose the customer in drop down menu the customer address was auto genrate

you can put this formula in cell B7 and copy down

=OFFSET('Purchase Customer'!\$C\$5,MATCH(\$B\$6,'Purchase Customer'!\$C\$6:\$C\$39,0),ROWS(\$A\$7:A7))

• ## If RESULT is up to 30 days THEN "Y", IF 31 and > then "N"

Re: If RESULT is up to 30 days THEN &quot;Y&quot;, IF 31 and &gt; then &quot;N&quot;

is this what you wnat

=IF(x4<=30,"Y","N")

for conitional format you can use this formula

=X4<=30

or

=X4="Y"

hope that helps

• ## Multiple Critera search

Re: Multiple Critera search

Do you really expect someone to help you when all you say is " I can not get the formula to work"

COUNTIFS will "work" as per the description given by you in your earlier post....if there are more complexities then you may kindly provide more explanation or even upload a dummy file so that you get a more calibrated response

• ## conditional formatting to highlight text only if it appears 6-10 times + IF command f

Re: conditional formatting to highlight text only if it appears 6-10 times + IF comma

not sure what you mean by "Entire Column" are you applying CondtlFormat to entire column....

in any case you can qualify the formula by highlighting only those cells where the search word comes within 6 and 10 times...hope that helps

• ## Multiple Critera search

Re: Multiple Critera search

you can use the COUNTIFS function...see the help file for more details

note it is COUNTIFS and not COUNTIF

• ## Reconciling dollar amounts between two workbooks by account number

Re: Reconciling dollar amounts between two workbooks by account number

have u tried the INDEX/MATCH

but generally any of the LOOKUP functions will work..see the help file or browse the numerous such posts we have here in this forum

• ## conditional formatting to highlight text only if it appears 6-10 times + IF command f

Re: conditional formatting to highlight text only if it appears 6-10 times + IF comma

you can use something like this =AND(COUNTIF(Range,"Active")>=6,COUNTIF(Range,"Active")<=10)

• ## Finding the average based a predefined criteria

Re: Finding the average based a predefined criteria

this formula works for me

=AVERAGEIFS('Report Format'!H5:H23,Ages,">="&C10,Ages,"<="&D10)

it gives value of 13.8 under pre-test

pl note that it seems there is some circluar in ur file and many times formulas return null values untile the Iterative Cal checkbox is ticked

hope that helps

• ## VBA: Input min value and return row values that meets min value (multiple sheets)

Re: VBA: Input min value and return row values that meets min value (multiple sheets)

see this code

note the following

the code takes an Output sheet for the output
Sheet1 takes the column count

change these references as reqd

• ## Finding a result based on a matrix. Solved by Pangolin

Re: Finding a result based on a matrix

if you are getting a NA error it means you are entering at least one value which is not getting matched...suggest you open Evaluate Formula dialogue box and find which value is being entered wrongly

• ## Finding a result based on a matrix. Solved by Pangolin

Re: Finding a result based on a matrix

this is one approach

note the three criteria are being inputted in cells B18 (utilisation) B19>> the % and B20 >> the decimal value

this formula assumes that FL/DR/X/Y are the same across all the four matrices....and hence it only finds the correct matrix but within that matrix it does not specifically search for the next two values but takes standardise values to search within that matrix

hope that helps

• ## List names that match criteria

Re: List names that match criteria

this is one approach

=IFERROR(INDEX(\$A\$2:\$A\$10,LARGE(ROW(\$A\$2:\$A\$10)*((B\$2:B\$10)="Y"),COUNTIF(B\$2:B\$10,"Y")-ROWS(\$A\$2:A2)+1)-ROW(\$A\$2)+1),"")

note this is an array formula and needs to be conformed by Ctrl+Shift+Enter

• ## Insurance Premium Payment Reminder for Agent

Re: Insurance Premium Payment Reminder for Agent

you can use this formula to generate the next due date for your policy as compared to Todays date

=EDATE(F2,(12/M2)*ROUNDUP((TODAY()-F2)/ROUND(365/M2,0),0))

note here Col F takes the Commencement Date and a new Column M has to be provided setting out the number of payments each year for a policy

see the attached file for more details

you can then use Conidtional Formatting to highlight payments for next months or so

• ## Open userform with values filled in based on selected row

Re: Open userform with values filled in based on selected row

from what I have understood you want the userform to populate with the record which you have selected

for that you can use something like this

Code
``````Private Sub UserForm_Initialize()

txtLastName.Value = ActiveCell.EntireRow.Cells(1, 3).Value

End Sub``````

this will populate the text box for 'last value'with the last name of the row which you have selected

you can write similar code for the other text boxes

hope that helps

• ## Creating Marco and Deleting Rows

Re: Creating Marco and Deleting Rows

you can for eg add a command button and put this code within the "Click" action

Code
``````Private Sub CommandButton1_Click()

'put the code here

End Sub``````

however note this code references the activesheet and will run only on the sheet where the command button is present

to make the code run for other sheets you will have to apprpriately set the sheet references..

• ## Sumproduct keeps giving me a problem, how can I formulate to skip blanks/errors

Re: Sumproduct keeps giving me a problem, how can I formulate to skip blanks/errors

see this syntax

=SUMPRODUCT(IF((ISERROR(C3:C13)=TRUE),0,((C3:C13)="ccc"))*(D3:D13))

note this is an array formula and needs to be conformed by Ctrl+Shift+Enter

• ## sum difference

Re: sum difference

but ur Cell G38 is a product of G30 (and following three cells) which is a sum of G19 and G20...hence putting formula in cell G20 will create a nasty circular and actually generate false values...Stephen's solution seems to be quite appropriate

• ## Creating Marco and Deleting Rows

Re: Creating Marco and Deleting Rows

see if this code helps

Code
``````Sub copypsaste()
Set myrange = ActiveSheet.UsedRange
myrange.Copy Destination:=nbook.Sheets("sheet1").Range("A1")
nbook.Activate
For Each cell In Sheets("sheet1").UsedRange
If cell.Value = "empty" Then cell.EntireRow.Delete
Next cell
End Sub``````
• ## Sumproduct keeps giving me a problem, how can I formulate to skip blanks/errors

Re: Sumproduct keeps giving me a problem, how can I formulate to skip blanks/errors

can u post an example file as it is difficult to recreate ur formula??

• ## Check if Match Numbers exist second sheet

Re: Nobody has been able to figure this out. its complex. to long to put in title

prima faciie it seems to be rather confusing and rambling as against complex

why dont you post an example file of what ur trying to do