Excel - Making references in a cell using text from another cell

Dr Pepper

Hi,

It's kind of awkward to explain what I'm looking for (and I have no idea what to type in a Google search!) so I will give a brief example and maybe somebody can help me:

- Start a new Workbook with 3 sheets (Sheet1, Sheet2 and Sheet3)
- I type '3' in cell A1 of Sheet1
- I type '5' in cell A1 of Sheet2
- In cell A1 of Sheet3, I can type 1 or 2
- In cell A2 of Sheet3, I want a formula to get the value of A1 in Sheet1 OR Sheet2 depending on the contents of A1 i.e. if A1 contains a 1, I want the formula to be '=Sheet1!A1'... if A1 contains a 2, I want the formula to be '=Sheet2!A1'

Basically, I need a way to make a dynamic type of formula in 1 cell (cell A2) using text from another cell (cell A1). Just to confuse my question further, the resulting formula in cell A2 might look something like this: '=Sheet(get value from A1)!A1'

Anybody still with me?
Thanks

Seth Brundle

This...

=INDIRECT("Sheet" &A1 & "!A1")

Dr Pepper

Perfect! - I've spend years wondering about that one :rolleyes:
Thanks

Seth Brundle

No Probs - just stick the cheque in the post!