Forum Discussion

baylism's avatar
baylism
Copper Contributor
May 16, 2019

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 i...
excel
Excel on Mac
Formulas and Functions
Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
May 16, 2019

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.

 

  • baylism's avatar
    baylism
    Copper Contributor
    May 16, 2019

    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.

     

     

    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      May 16, 2019

      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

       

    • Twifoo's avatar
      Twifoo
      Silver Contributor
      May 16, 2019
      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.

Resources