Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching formula - Can we have a variable field for the row number
Hello
I have the following matching formula that works fine and than another statement that does a fill down using br to know when to stop the fill down. Is there someway to modify the matching formula or to use a Lookup formula that I can tell it what row to stop at. I count the number of rows in a previous worksheet to know how many rows there are but I don't know how to substitute that variable in place of that 8000 count I have. Each week we run this report we could have differing number of rows. I just represent it with a higher number because I don't know what else to do. Cells(2, "k") = "=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))" Cells(2, "k").AutoFill Destination:=Range(Cells(2, "k"), Cells(br, "k")) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching formula - Can we have a variable field for the row number
Suppose you want to have this in B1
=Sum(A1:A100) and you want to change 100 to whatever you have in C1 then use this instead (in B1) ==SUM(INDIRECT("A1:A" & C1)) It will give you the same result as the first formula above if C1 has 100 Replace the protion =IF($b$2:$b$8000 with =IF(INDIRECT("$b$2:$b$" & C1) where C1 should contain 8000 or what you want... you may have to play arond a little but the concept is simple as explained above... You can adjust your formula using the same method... "Bud" wrote: Hello I have the following matching formula that works fine and than another statement that does a fill down using br to know when to stop the fill down. Is there someway to modify the matching formula or to use a Lookup formula that I can tell it what row to stop at. I count the number of rows in a previous worksheet to know how many rows there are but I don't know how to substitute that variable in place of that 8000 count I have. Each week we run this report we could have differing number of rows. I just represent it with a higher number because I don't know what else to do. Cells(2, "k") = "=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))" Cells(2, "k").AutoFill Destination:=Range(Cells(2, "k"), Cells(br, "k")) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching formula - Can we have a variable field for the row nu
I just can't get this to work...I tried so many combinations with using the
INDIRECT Can you write the formula that will work using the INDIRECT...The following formula is what is working that I want to substitute out the 8000 Cells(2, "k") = "=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))" "Sheeloo" wrote: Suppose you want to have this in B1 =Sum(A1:A100) and you want to change 100 to whatever you have in C1 then use this instead (in B1) ==SUM(INDIRECT("A1:A" & C1)) It will give you the same result as the first formula above if C1 has 100 Replace the protion =IF($b$2:$b$8000 with =IF(INDIRECT("$b$2:$b$" & C1) where C1 should contain 8000 or what you want... you may have to play arond a little but the concept is simple as explained above... You can adjust your formula using the same method... "Bud" wrote: Hello I have the following matching formula that works fine and than another statement that does a fill down using br to know when to stop the fill down. Is there someway to modify the matching formula or to use a Lookup formula that I can tell it what row to stop at. I count the number of rows in a previous worksheet to know how many rows there are but I don't know how to substitute that variable in place of that 8000 count I have. Each week we run this report we could have differing number of rows. I just represent it with a higher number because I don't know what else to do. Cells(2, "k") = "=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))" Cells(2, "k").AutoFill Destination:=Range(Cells(2, "k"), Cells(br, "k")) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching formula - Can we have a variable field for the row nu
Can you send me the file as it will be easier to test too...
"Bud" wrote: I just can't get this to work...I tried so many combinations with using the INDIRECT Can you write the formula that will work using the INDIRECT...The following formula is what is working that I want to substitute out the 8000 Cells(2, "k") = "=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))" "Sheeloo" wrote: Suppose you want to have this in B1 =Sum(A1:A100) and you want to change 100 to whatever you have in C1 then use this instead (in B1) ==SUM(INDIRECT("A1:A" & C1)) It will give you the same result as the first formula above if C1 has 100 Replace the protion =IF($b$2:$b$8000 with =IF(INDIRECT("$b$2:$b$" & C1) where C1 should contain 8000 or what you want... you may have to play arond a little but the concept is simple as explained above... You can adjust your formula using the same method... "Bud" wrote: Hello I have the following matching formula that works fine and than another statement that does a fill down using br to know when to stop the fill down. Is there someway to modify the matching formula or to use a Lookup formula that I can tell it what row to stop at. I count the number of rows in a previous worksheet to know how many rows there are but I don't know how to substitute that variable in place of that 8000 count I have. Each week we run this report we could have differing number of rows. I just represent it with a higher number because I don't know what else to do. Cells(2, "k") = "=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))" Cells(2, "k").AutoFill Destination:=Range(Cells(2, "k"), Cells(br, "k")) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching formula - Can we have a variable field for the row number
I think your formula is wrong.
I think you'd want something like: =IF($B2="","",INDEX(Personnel!$B$1:$B$99,MATCH($B2 ,Personnel!$A$1:$A$99,0))) (single cell to start the =if(), a single cell to match (B2, not B2:B8000) and $b$2 is changed to $b2 in both spots.) If you can pick out a column on the personnel worksheet that always has data in it so you can use it to find the last row, then you can use something like: Dim LastRow As Long With Worksheets("SomeNameHere") 'or With Activesheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("K2").Formula _ = "=IF($b2="""",""""," _ & "INDEX(Personnel!$B$1:$B$" & LastRow & ",MATCH($b2," _ & "Personnel!$A$1:$A$" & LastRow & ",0)))" End With Personally, I'd just use the entire column: With Worksheets("SomeNameHere") 'or With Activesheet .Range("K2").Formula _ = "=IF($b$2="""",""""," _ & "INDEX(Personnel!$B:$B,MATCH($b$2," _ & "Personnel!$A:$A,0)))" End With And instead of using .autofill, I like to fill the entire range at once. Dim LastRow As Long With Worksheets("SomeNameHere") 'or With Activesheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("K2").Formula _ = "=IF($b2="""",""""," _ & "INDEX(Personnel!$B$1:$B$" & LastRow & ",MATCH($b2," _ & "Personnel!$A$1:$A$" & LastRow & ",0)))" End With Or using the entire column: Dim LastRow As Long With Worksheets("SomeNameHere") 'or With Activesheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("K2:K" & LastRow).Formula _ = "=IF($b2="""",""""," _ & "INDEX(Personnel!$B:$B,MATCH($b2," _ & "Personnel!$A:$A,0)))" End With Bud wrote: Hello I have the following matching formula that works fine and than another statement that does a fill down using br to know when to stop the fill down. Is there someway to modify the matching formula or to use a Lookup formula that I can tell it what row to stop at. I count the number of rows in a previous worksheet to know how many rows there are but I don't know how to substitute that variable in place of that 8000 count I have. Each week we run this report we could have differing number of rows. I just represent it with a higher number because I don't know what else to do. Cells(2, "k") = "=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))" Cells(2, "k").AutoFill Destination:=Range(Cells(2, "k"), Cells(br, "k")) -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching formula - Can we have a variable field for the row number
Ignore that last post.
I was using the lastrow variable for the lastrow in both worksheets. That won't work for you. I think your formula is wrong. I think you'd want something like: =IF($B2="","",INDEX(Personnel!$B$1:$B$99,MATCH($B2 ,Personnel!$A$1:$A$99,0))) (single cell to start the =if(), a single cell to match (B2, not B2:B8000) and $b$2 is changed to $b2 in both spots.) If you can pick out a column on the personnel worksheet that always has data in it so you can use it to find the last row (and instead of using .autofill, I like to fill the entire range at once), then you can do something like: Dim LastRowP As Long 'Personnel sheet's last row Dim LastRowS as Long 'SomeNameHere sheet's last row With worksheets("Personnel") lastrowP = .cells(.rows.count,"A").end(xlup).row end with With Worksheets("SomeNameHere") 'or With Activesheet lastrowS = .cells(.rows.count,"A").end(xlup).row .Range("K2:K" & lastrowS).Formula _ = "=IF($b2="""",""""," _ & "INDEX(Personnel!$B$1:$B$" & LastRowP & ",MATCH($b2," _ & "Personnel!$A$1:$A$" & LastRowP & ",0)))" End With Or using the entire column: Dim LastRowS as long 'no need for the personnel last row in this! With Worksheets("SomeNameHere") 'or With Activesheet LastRowS = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("K2:K" & LastRowS).Formula _ = "=IF($b2="""",""""," _ & "INDEX(Personnel!$B:$B,MATCH($b2," _ & "Personnel!$A:$A,0)))" End With (I deleted the single cell formula. When I was changing my post, I realized that that was just adding confusion.) Bud wrote: Hello I have the following matching formula that works fine and than another statement that does a fill down using br to know when to stop the fill down. Is there someway to modify the matching formula or to use a Lookup formula that I can tell it what row to stop at. I count the number of rows in a previous worksheet to know how many rows there are but I don't know how to substitute that variable in place of that 8000 count I have. Each week we run this report we could have differing number of rows. I just represent it with a higher number because I don't know what else to do. Cells(2, "k") = "=IF($b$2:$b$8000="""","""",(INDEX(Personnel!$B$1: $B$1000,MATCH($b$2:$b$8000,Personnel!$A$1:$A$1000, 0))))" Cells(2, "k").AutoFill Destination:=Range(Cells(2, "k"), Cells(br, "k")) -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable row number in Sum($A$1:A10) formula | Excel Discussion (Misc queries) | |||
Macro - Formula RC[?] with ? being a variable number | Excel Discussion (Misc queries) | |||
data linking/field matching/formula updating long question | Excel Discussion (Misc queries) | |||
Selecting and pasting formula into a variable field size! | Excel Programming | |||
Need to insert formula to a variable number of rows | Excel Programming |