EXCEL CELL REFERENCES
EXCEL CELL REFERENCES
(OP)
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 "SHEET2" 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 "SHEET2">'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.





RE: EXCEL CELL REFERENCES
RE: EXCEL CELL REFERENCES
Col A Col B COl C
Row1 Sheet Cell Value
Row2 Sheet2 A1 =INDIRECT("'"&A2&"'!"&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.