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

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies