Compound Reference
I have a spreadsheet that has 3 columns. I need to be able to make what I
call a compound reference. It's really hard to explain but I need to do the following: ='Sheet A'!X(b1) whe The current sheet is Sheet B Sheet A is the sheet containing the cell needed X(b1) refers to column X of sheet A and the (b1) refers to the cell in sheet B that contains the row number to use in sheet A for row X Thanks in advance for your help! |
Compound Reference
=indirect("'sheet a'!x" & b1)
or =index('sheet a'!x:x,b1) The =index() formula is better--it only recalculates when something changes in column X of sheet a (or b1 changes). The =indirect() formula will recalc whenever excel recalculates. PeterM wrote: I have a spreadsheet that has 3 columns. I need to be able to make what I call a compound reference. It's really hard to explain but I need to do the following: ='Sheet A'!X(b1) whe The current sheet is Sheet B Sheet A is the sheet containing the cell needed X(b1) refers to column X of sheet A and the (b1) refers to the cell in sheet B that contains the row number to use in sheet A for row X Thanks in advance for your help! -- Dave Peterson |
Compound Reference
Perfect!
thank you Dave. "Dave Peterson" wrote: =indirect("'sheet a'!x" & b1) or =index('sheet a'!x:x,b1) The =index() formula is better--it only recalculates when something changes in column X of sheet a (or b1 changes). The =indirect() formula will recalc whenever excel recalculates. PeterM wrote: I have a spreadsheet that has 3 columns. I need to be able to make what I call a compound reference. It's really hard to explain but I need to do the following: ='Sheet A'!X(b1) whe The current sheet is Sheet B Sheet A is the sheet containing the cell needed X(b1) refers to column X of sheet A and the (b1) refers to the cell in sheet B that contains the row number to use in sheet A for row X Thanks in advance for your help! -- Dave Peterson . |
All times are GMT +1. The time now is 04:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com