Forum Discussion
Changing cell reference in a formula using contents of another cell
Hello All,
I have a spreadsheet with 3 tabs, Sheet1, Sheet2 and Sheet3. On Sheet3 I have lots of cells with formulas that refer to cells in Sheet1. Suppose I now want them all to refer to Sheet2 instead. Better than Find/Replace, or doing it manually, can I set things up so that in Sheet 3 I have a cell where I enter either 'Sheet1' or 'Sheet2' as text, and the formulae construct the cell references from it?
Write 'Sheet2' in cell A1
Have a formula in another cell something like ='A1'!B7
and now get the value for Sheet2! B7
4 Replies
Microsoft invented INDIRECT() for this purpose.
But beware. The function is volatile. If INDIRECT() is used any change in the workbook triggers a recalculation of all the cells in the workbook.
Detlef_Lewin I read about indiect() before, and it seems to be a bit different. If I have 27 in cell B2, and 'B2' in cell A1, then indirect(A1) looks in A1, finds 'B2' and gives me the value 27.
I want it to look at a cell which contains text, and embed that text into a cell reference. ie, the text in A1 is a sheet name, and I write xxxxx - A1 xxxx B10 in a cell, and it returns the value in sheet 'A1' cell B10.
INDIRECT() is designed to convert text into a reference. Pleasesee the support page for the function.
=INDIRECT("'"&A1&"'!B10")This concatenates the value in A1 with some additional text to become the following reference.
'Sheet1'!B10
