EXCEL CELL REFERENCES

[gwm]

I have a multi-page Excel worksheet in which I have references to cells on other sheets, e.g. ='SHEET2'A1. Is there a way to have the &quot;SHEET2&quot; page reference point to a cell instead with the cell contents having the page I wish the reference to point to. In other words, the formula ='SHEET2'A1 would change to '<expression for a cell reference where the cell contains &quot;SHEET2&quot;>'A1. The end result would be the sheet referred to in the formula cell reference would be dependent on a value contained in a cell that I include in the expression, which could be SHEET2, SHEET3,etc. I have tried different approaches, but get only errors.

Any ideas or suggestions? Thanks.

 

[macajm]

try using the INDIRECT function.(see Excel Help)

 

[bltseattle]

Consider the following:

Col A Col B COl C
Row1 Sheet Cell Value
Row2 Sheet2 A1 =INDIRECT(&quot;'&quot;&A2&&quot;'!&quot;&B2)

So we are building the reference with text concatenation functions, then looking up the value with INDIRECT.

I love the indirect function. Once you get a feel for how it works its surprising how many uses it can have, and it can be an elegant simplification for what might otherwise take several operations.