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 selection
When A4 is set to "A1", then A3 should return 5
. When A4 is set to "A2", A3 should return 10
.
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'!A2
represents the same sort of information as is represented by the value in 'Sheet2'!A2
. I want to build another sheet, named Comparison
, 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=Sheet3
Then the formula in 'Comparison!'A2
should 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 IF
statements) in Excel?