I have a cell in Excel, to which I want to pull data from another cell that is defined as a value in a third cell.
For example:
A1=
5
A2=10
A3=insert syntax here, using A4 as source for cell reference
A4=user-input selectionWhen A4 is set to “A1”, then A3 should return
5. When A4 is set to “A2”, A3 should return10.The above is actually a bit of an over-simplification. What I’m really looking to do, is to use one cell to define which sheet to pull data from for calculations that will be performed in another cell.
Scenario:
A workbook contains several sheets (
Sheet1,Sheet2,Sheet3, etc.) which are laid out identically – a value in'Sheet1'!A2represents the same sort of information as is represented by the value in'Sheet2'!A2. I want to build another sheet, namedComparison, that allows me to easily compare data between two chosen sheets. This sheet will also be laid out identically to the others, but the values here should be calculations of the difference between the similar values in the target sheets. This sheet will also have two special cells which are configured with data validation and in-cell drop-downs, to allow selection of two other sheets, by name, for comparison.Let’s say the selection cells are in A5 and A6.
Given:
A5=
Sheet5
A6=Sheet3Then the formula in
'Comparison!'A2should result in the same as'Sheet5'!A2-'Sheet3'!A2. If I change A6 to “Sheet7”, then the formula should adjust to'Sheet5'!A2-'Sheet7'A2.Is this sort of dynamic cell referencing easily doable (without a crap-ton of
IFstatements) in Excel?
Solution:
The first part of your question can be solved by using the INDIRECT function. If cell A3 contains this formula =INDIRECT(A4) then it will return the value of the cell specified in A4.
You could put a formula like this =A5&"!A1" together in cell A7 and then use an INDIRECT statement to reference A7. This would allow you to change only the sheet name in A5 but it would update the entire formula reference in whatever cell you had it in.