formulas - multiple reference sheets
 

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

--
Message posted from http://www.ExcelForum.com

formulas - multiple reference sheets
 

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)

--
Regards,
Tom Ogilvy



"jws217 " wrote in message
...
hi everyone,
I am trying to write a formula in a cell that references a range on a
separate worksheet. I want the range to be from a value passed on the
current worksheet to a constant value. Some examples to help decode
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 thing
that really changes is the column index "D". The row index is a
constant "5". Is there a way to pass the value on sheet1 to this
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 on
sheet2 rather that have the values on sheet1 automatically update with
the data on sheet2.

thanks
j-rod


---
Message posted from http://www.ExcelForum.com/

formulas - multiple reference sheets
 

BRILLIANT!!!!!!!

thank

--
Message posted from http://www.ExcelForum.com