Forum Discussion

warrevar's avatar
warrevar
Brass Contributor
Jan 06, 2021

Structured References - How to Keep Them From Toggling Off?

Excel keeps toggling off my formula structured references.  For instance, I input the following formula:

 

=INDEX(_TABLE3[COLOR],MATCH($B5,_TABLE3[NAME],0))

 

Then I save / close the file.  When I reopen the file, it has changed the formula to this:

 

=INDEX('TAB 3'!$G$5:$G$4471,MATCH($B5,'TAB 3'!$B$5:$B$4471,0))

 

How do I prevent this from happening?

excel
Formulas and Functions

5 Replies

  • Jihad Al-Jarady's avatar
    Jihad Al-Jarady
    MCT
    Jan 06, 2021

    Hi warrevar 

     

    From File --> Options --> from the left menu click on formula --> under the working with formula

    make sure the option "Use table names in formula" is checked

     

     

    * If my post helped you, please click on like.

    • warrevar's avatar
      warrevar
      Brass Contributor
      Jan 07, 2021

      Jihad Al-Jarady I'm using excel 2010, which has different menus.  Unfortunately, this does not seem to be an option in this earlier version.

      • Jihad Al-Jarady's avatar
        Jihad Al-Jarady
        MCT
        Jan 07, 2021

        warrevar 

         

        Is that happening with all the worksheets? or just one?

        If it is with one worksheet, did you convert the table range? structured references works with tables