Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formulas - multiple reference sheets
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing cell reference on multiple sheets in one go.... | Excel Worksheet Functions | |||
Pivot Table to reference multiple sheets | Charts and Charting in Excel | |||
Summing across multiple sheets using a changeable reference cell | Excel Worksheet Functions | |||
How reference multiple sheets in a formula | Excel Discussion (Misc queries) | |||
Cell Reference, Multiple Sheets | Excel Worksheet Functions |