Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO: Add formula's across row if criteria is met
Hello !,
In effort to reduce size of lookup, dependent workbook, I would like to have formulas placed across columns A:F in dependent worksheet if 2 cell values criterias are met. A1 = find first row in 'Source' worksheet where criteria value is met column A B1 = find last row in 'Source' worksheet where value is present column F column A 'Source' worksheet = date value column F 'Source' worksheet = final update for row. Trying to keep question simple, hopefully not to simple. thanks for any help, Steven / Dallas |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO: Add formula's across row if criteria is met
=match(a1,Source!a:a,0)
will give you the row number where A1 first matches something in column A of Source. =LOOKUP(2,1/(Source!F1:F999=B1),ROW(Source!F1:F999)) (Make that 999 big enough to extend past the last possible row--but don't use the whole column.) will return the row of the last matching value (value in B1) in source F1:f999. SteveT wrote: Hello !, In effort to reduce size of lookup, dependent workbook, I would like to have formulas placed across columns A:F in dependent worksheet if 2 cell values criterias are met. A1 = find first row in 'Source' worksheet where criteria value is met column A B1 = find last row in 'Source' worksheet where value is present column F column A 'Source' worksheet = date value column F 'Source' worksheet = final update for row. Trying to keep question simple, hopefully not to simple. thanks for any help, Steven / Dallas -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO: Add formula's across row if criteria is met
If you really need a macro, you can use those formulas in the code, too.
Option Explicit Sub testme() Dim res1 As Variant Dim res2 As Variant res1 = Application.Match("somevalue", Worksheets("Source").Range("a:a"), 0) If IsError(res1) Then MsgBox "Not found" Else MsgBox "found on row: " & res1 End If res2 = Application.Evaluate _ ("lookup(2,1/(Source!F1:F999=""x""),ROW(Source!F1:F999))") If IsError(res2) Then MsgBox "Not found" Else MsgBox "found on row: " & res2 End If End Sub SteveT wrote: Hello !, In effort to reduce size of lookup, dependent workbook, I would like to have formulas placed across columns A:F in dependent worksheet if 2 cell values criterias are met. A1 = find first row in 'Source' worksheet where criteria value is met column A B1 = find last row in 'Source' worksheet where value is present column F column A 'Source' worksheet = date value column F 'Source' worksheet = final update for row. Trying to keep question simple, hopefully not to simple. thanks for any help, Steven / Dallas -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
MACRO: Add formula's across row if criteria is met
Thanks for the response Dave,
I'm actualling looking for the macro which will add the formula down the rows if criteria met. Brgds, Steven "Dave Peterson" wrote: =match(a1,Source!a:a,0) will give you the row number where A1 first matches something in column A of Source. =LOOKUP(2,1/(Source!F1:F999=B1),ROW(Source!F1:F999)) (Make that 999 big enough to extend past the last possible row--but don't use the whole column.) will return the row of the last matching value (value in B1) in source F1:f999. SteveT wrote: Hello !, In effort to reduce size of lookup, dependent workbook, I would like to have formulas placed across columns A:F in dependent worksheet if 2 cell values criterias are met. A1 = find first row in 'Source' worksheet where criteria value is met column A B1 = find last row in 'Source' worksheet where value is present column F column A 'Source' worksheet = date value column F 'Source' worksheet = final update for row. Trying to keep question simple, hopefully not to simple. thanks for any help, Steven / Dallas -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for Looping for Criteria Checking | Excel Discussion (Misc queries) | |||
Create macro to filter on multiple criteria | Excel Worksheet Functions | |||
Macro to hide and unhide based on criteria | Excel Discussion (Misc queries) | |||
Reg autofiltering using macro in excel with out providing criteria | Excel Worksheet Functions | |||
Can a macro erase it's self after a set criteria? | Excel Worksheet Functions |