Home

Changing cell reference in a formula using contents of another cell

%3CLINGO-SUB%20id%3D%22lingo-sub-576498%22%20slang%3D%22en-US%22%3EChanging%20cell%20reference%20in%20a%20formula%20using%20contents%20of%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-576498%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20with%203%20tabs%2C%20Sheet1%2C%20Sheet2%20and%20Sheet3.%26nbsp%3B%20On%20Sheet3%20I%20have%20lots%20of%20cells%20with%20formulas%20that%20refer%20to%20cells%20in%20Sheet1.%26nbsp%3B%20Suppose%20I%20now%20want%20them%20all%20to%20refer%20to%20Sheet2%20instead.%26nbsp%3B%20Better%20than%20Find%2FReplace%2C%20or%20doing%20it%20manually%2C%20can%20I%20set%20things%20up%20so%20that%20in%20Sheet%203%20I%20have%20a%20cell%20where%20I%20enter%20either%20'Sheet1'%20or%20'Sheet2'%20as%20text%2C%20and%20the%20formulae%20construct%20the%20cell%20references%20from%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWrite%20'Sheet2'%20in%20cell%20A1%3C%2FP%3E%3CP%3EHave%20a%20formula%20in%20another%20cell%20something%20like%20%3D'A1'!B7%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20now%20get%20the%20value%20for%20Sheet2!%20B7%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-576498%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-576562%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20cell%20reference%20in%20a%20formula%20using%20contents%20of%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-576562%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F343393%22%20target%3D%22_blank%22%3E%40baylism%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMicrosoft%20invented%20INDIRECT()%20for%20this%20purpose.%3C%2FP%3E%3CP%3EBut%20beware.%20The%20function%20is%20volatile.%20If%20INDIRECT()%20is%20used%20any%20change%20in%20the%20workbook%20triggers%20a%20recalculation%20of%20all%20the%20cells%20in%20the%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-576765%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20cell%20reference%20in%20a%20formula%20using%20contents%20of%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-576765%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BI%20read%20about%20indiect()%20before%2C%20and%20it%20seems%20to%20be%20a%20bit%20different.%26nbsp%3B%20If%20I%20have%2027%20in%20cell%20B2%2C%20and%20'B2'%20in%20cell%20A1%2C%20then%20indirect(A1)%20looks%20in%20A1%2C%20finds%20'B2'%20and%20gives%20me%20the%20value%2027.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20it%20to%20look%20at%20a%20cell%20which%20contains%20text%2C%20and%20embed%20that%20text%20into%20a%20cell%20reference.%26nbsp%3B%20ie%2C%20the%20text%20in%20A1%20is%20a%20sheet%20name%2C%20and%20I%20write%26nbsp%3B%20xxxxx%20-%20A1%20xxxx%20B10%20in%20a%20cell%2C%20and%20it%20returns%20the%20value%20in%20sheet%20'A1'%20cell%20B10.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-576837%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20cell%20reference%20in%20a%20formula%20using%20contents%20of%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-576837%22%20slang%3D%22en-US%22%3EI%E2%80%99m%20sure%20my%20friend%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%20can%20provide%20you%20an%20example%20for%20that.%20I%20suggest%20that%20the%20Sheet%20Name%20be%20selected%20from%20drop-down%20list%2C%20then%20use%20INDIRECT%2C%20which%20is%20a%20volatile%20function.%20In%20layman%E2%80%99s%20terms%2C%20volatile%20in%20Excel%20means%20very%20slow%20because%20it%20triggers%20recalculation%20almost%20whenever%20a%20slight%20modification%20of%20the%20data%20is%20made.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-577148%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20cell%20reference%20in%20a%20formula%20using%20contents%20of%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-577148%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F343393%22%20target%3D%22_blank%22%3E%40baylism%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EINDIRECT()%20is%20designed%20to%20convert%20text%20into%20a%20reference.%20Pleasesee%26nbsp%3B%20the%20support%20page%20for%20the%20function.%3C%2FP%3E%3CPRE%3E%3DINDIRECT(%22'%22%26amp%3BA1%26amp%3B%22'!B10%22)%3C%2FPRE%3E%3CP%3EThis%20concatenates%20the%20value%20in%20A1%20with%20some%20additional%20text%20to%20become%20the%20following%20reference.%3C%2FP%3E%3CPRE%3E'Sheet1'!B10%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
baylism
New Contributor

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

@baylism 

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.

 

 

I’m sure my friend @Detlef Lewin can provide you an example for that. I suggest that the Sheet Name be selected from drop-down list, then use INDIRECT, which is a volatile function. In layman’s terms, volatile in Excel means very slow because it triggers recalculation almost whenever a slight modification of the data is made.

@baylism 

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