dynamic cell references in formulas

[Guest]

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!

 

[Bob Phillips]

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)

 

[Guest]

Bob,

Awesome! Works great! Thanks! I now know how to get this and similar
things to work.


Carol