Posts by pangolin

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))

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

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

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

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

Re: Multiple Critera search

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

note it is COUNTIFS and not COUNTIF

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

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)

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

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

Sub datamine()
Dim ws As Worksheet
Dim myarray()
myvalue = Sheets("Output").Range("B2")
With Sheets("Sheet1")
cl = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
For Each ws In Worksheets
x = Z
If ws.Name <> "Output" Then
With ws
Set myrange = .Range("F2", .Range("F" & Rows.Count).End(xlUp))
End With
For Each cell In myrange
If cell.Value >= myvalue Then
x = x + 1
ReDim Preserve myarray(cl, x)
For i = 1 To cl
j = i - (cl - 1)
myarray(i, x) = cell.Offset(0, j).Value
Next i
End If
Next cell
Z = x
End If
Next ws
Sheets("Output").Range("A6") = x
For i = 1 To x
For j = 1 To cl
Sheets("Output").Range("A7").Offset(i, j - 1) = myarray(j, i)
Next j
Next i
End Sub

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

Re: Finding a result based on a matrix

this is one approach

=OFFSET(INDIRECT(ADDRESS(IFERROR(MATCH(B18,C1:C10,0),MATCH(B18,L1:L10,0)),IFERROR(MATCH(B18,A2:L2,0),MATCH(B18,A10:L10,0)))),MATCH(B19,B4:B7,0)+1,MATCH(B20,C3:I3,0)-1)*500

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

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

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

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

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

Re: Creating Marco and Deleting Rows

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

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..

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

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

Re: Creating Marco and Deleting Rows

see if this code helps

Sub copypsaste()
Set myrange = ActiveSheet.UsedRange
Set nbook = Workbooks.Add
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

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??

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