Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic cell references in formulas
I'd love to find an elegant way to do something.
I've got weekly information appended below each other in one sheet, and I'm pulling data from it in another by using VLOOKUP. Right now I get the beginning and ending rows for the lookup range, but have to manually input them into the formula -- is there a way to build the array automatically for the formula? I tried building and assembling text to represent the wanted rows, but got errors. The trick is that the number of rows is not constant from one week to the next. Here's what I'm using now (manually setting rows in the third calc based on values returned from the first two calcs): beginning row=+VLOOKUP(W$36,'0740'!$A4:$D750,4,FALSE) (look for the start date of the week, say 372 in this case) ending row=+VLOOKUP(W$36+7,'0740'!$A4:$D750,4,FALSE) (look for the start date of the next week, say 396 in this case) the value lookup=+VLOOKUP($A40,'0740'!$A$372:$D$396,3,0) (look for the value within the row range) Any help to not have to manually update rows for future weeks will be appreciated! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic cell references in formulas
Not tested, but I think this does it
=VLOOKUP($A40,INDIRECT("'0740'!$A$"&VLOOKUP(W$36,' 0740'!$A4:$D750,4,FALSE)&" :$D$"&VLOOKUP(W$36+7,'0740'!$A4:$D750,4,FALSE)),3, 0) -- HTH RP (remove nothere from the email address if mailing direct) "CarolM" wrote in message ... I'd love to find an elegant way to do something. I've got weekly information appended below each other in one sheet, and I'm pulling data from it in another by using VLOOKUP. Right now I get the beginning and ending rows for the lookup range, but have to manually input them into the formula -- is there a way to build the array automatically for the formula? I tried building and assembling text to represent the wanted rows, but got errors. The trick is that the number of rows is not constant from one week to the next. Here's what I'm using now (manually setting rows in the third calc based on values returned from the first two calcs): beginning row=+VLOOKUP(W$36,'0740'!$A4:$D750,4,FALSE) (look for the start date of the week, say 372 in this case) ending row=+VLOOKUP(W$36+7,'0740'!$A4:$D750,4,FALSE) (look for the start date of the next week, say 396 in this case) the value lookup=+VLOOKUP($A40,'0740'!$A$372:$D$396,3,0) (look for the value within the row range) Any help to not have to manually update rows for future weeks will be appreciated! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic cell references in formulas
Bob,
Awesome! Works great! Thanks! I now know how to get this and similar things to work. Carol "Bob Phillips" wrote: Not tested, but I think this does it =VLOOKUP($A40,INDIRECT("'0740'!$A$"&VLOOKUP(W$36,' 0740'!$A4:$D750,4,FALSE)&" :$D$"&VLOOKUP(W$36+7,'0740'!$A4:$D750,4,FALSE)),3, 0) -- HTH RP (remove nothere from the email address if mailing direct) "CarolM" wrote in message ... I'd love to find an elegant way to do something. I've got weekly information appended below each other in one sheet, and I'm pulling data from it in another by using VLOOKUP. Right now I get the beginning and ending rows for the lookup range, but have to manually input them into the formula -- is there a way to build the array automatically for the formula? I tried building and assembling text to represent the wanted rows, but got errors. The trick is that the number of rows is not constant from one week to the next. Here's what I'm using now (manually setting rows in the third calc based on values returned from the first two calcs): beginning row=+VLOOKUP(W$36,'0740'!$A4:$D750,4,FALSE) (look for the start date of the week, say 372 in this case) ending row=+VLOOKUP(W$36+7,'0740'!$A4:$D750,4,FALSE) (look for the start date of the next week, say 396 in this case) the value lookup=+VLOOKUP($A40,'0740'!$A$372:$D$396,3,0) (look for the value within the row range) Any help to not have to manually update rows for future weeks will be appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell references in formulas | Excel Discussion (Misc queries) | |||
Dynamic Cell References in Excel | Excel Discussion (Misc queries) | |||
Dynamic formulas including worksheet references | Excel Worksheet Functions | |||
dynamic cell references | Excel Worksheet Functions | |||
Dynamic Cell References | Excel Programming |