5

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?

Improve this question

1 Answer 1

Reset to default
7

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.

Improve this answer
5
  • Just typing "INDIRECT" into the formula field, for the help text, I see it looks for a formula as specified by a text string. I wonder if I could nest a CONCATENATE in there?
    – Iszi
    Oct 17, 2012 at 19:00
  • Tested: CONCATENATE works inside of INDIRECT. Now to check if it can do calculations that way.
    – Iszi
    Oct 17, 2012 at 19:09
  • You can do formulas with it. See my edited answer. You can then do a forumla like this: =INDIRECT(A7)-INDIRECT(A8) to do what it seems you are looking to do.
    – techturtle
    Oct 17, 2012 at 19:11
  • 1
    Thanks, but I'm not sure why you're suggesting another cell. For the given scenario, the solution for A2 is =INDIRECT(CONCATENATE("'",A5,"'!A2"))-INDIRECT(CONCATENATE("'",A6,"'!A2")). You especially want to include those single quotes, because Excel will need them for sheet names containing certain characters (like a dash). Now that I think of it, I see why you suggested another cell - you weren't using CONCATENATE. CONCATENATE allows you to put together text strings within a formula that accepts text, like INDIRECT.
    – Iszi
    Oct 17, 2012 at 19:16
  • Yeah. You had a very descriptive question, but it's still hard to envision exactly what you plan to do with it without being able to see the sheets themselves. I haven't had to use CONCATENATE very often; for me & works in most cases. But it does make the formula kind of "inside out" compared to yours, and does necessitate the extra cells.
    – techturtle
    Oct 17, 2012 at 19:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .