Thread: Reference Sheet

If I have a cell that has as its contents as sheet name, is there anyway I can use the cell's address to reference that sheet?
As an example, say I have in Sheet1, cell A1, the text Sheet2. And let's say I want to return the value of cell B2 on whichever sheet the text of A1 says.
So, on Sheet1, I might have this:

Code:

   A       
1  Sheet2
2

And on Sheet2, I might have this:

Code:

  A B
1
2   purple

And on Sheet3, I might have this:

Code:

  A   B
1
2     red

So, let's say I want to, in Sheet1 A2 to return the color in the sheet said in A1. So, if A1 said Sheet2, A2 returns purple, and if A1 said Sheet3, A2 returns red.
Is this possible without using if statements (ie. just the address of the cell, A1)? I'm sorry I can't word this any better.

Hi nesthead98,

Try this in cell A2 of Sheet1:

Code:

=INDIRECT(ADDRESS(2,1,1,1,A1))

Cheers,