formulas - multiple reference sheets

J

jws217

hi everyone,
I am trying to write a formula in a cell that references a range on
separate worksheet. I want the range to be from a value passed on th
current worksheet to a constant value. Some examples to help decod
this question:

MATCH($A2,sheet2!(D5):$AD$5,0)+($C$18-1)

where (D5) is a cell value on sheet1 that is dynamic. The only thin
that really changes is the column index "D". The row index is
constant "5". Is there a way to pass the value on sheet1 to thi
formula that references sheet2? I have found a way to do it using VB
however, I would have to run the script everytime the values change o
sheet2 rather that have the values on sheet1 automatically update wit
the data on sheet2.

thanks
j-ro
 
T

Tom Ogilvy

Assume the start cell address is in cell A2 of Sheet1 (in your example it
would contain the string D5 as I understand the problem).

=Match(sheet1!$A$2,Indirect("Sheet2" & Sheet1!$A$2 & ":AD5"),0)+$C$18-1)
 
Top