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: 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
Display More
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
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
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