Structured References - How to Keep Them From Toggling Off?

%3CLINGO-SUB%20id%3D%22lingo-sub-2038300%22%20slang%3D%22en-US%22%3EStructured%20References%20-%20How%20to%20Keep%20Them%20From%20Toggling%20Off%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2038300%22%20slang%3D%22en-US%22%3E%3CP%3EExcel%20keeps%20toggling%20off%20my%20formula%20structured%20references.%26nbsp%3B%20For%20instance%2C%20I%20input%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-align-center%22%3E%3DINDEX(_TABLE3%5BCOLOR%5D%2CMATCH(%24B5%2C_TABLE3%5BNAME%5D%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20I%20save%20%2F%20close%20the%20file.%26nbsp%3B%20When%20I%20reopen%20the%20file%2C%20it%20has%20changed%20the%20formula%20to%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-align-center%22%3E%3DINDEX('TAB%203'!%24G%245%3A%24G%244471%2CMATCH(%24B5%2C'TAB%203'!%24B%245%3A%24B%244471%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20prevent%20this%20from%20happening%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2038300%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2038452%22%20slang%3D%22en-US%22%3ERe%3A%20Structured%20References%20-%20How%20to%20Keep%20Them%20From%20Toggling%20Off%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2038452%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F286940%22%20target%3D%22_blank%22%3E%40warrevar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20%3CSTRONG%3EFile%3C%2FSTRONG%3E%20--%26gt%3B%20%3CSTRONG%3EOptions%3C%2FSTRONG%3E%20--%26gt%3B%20from%20the%20left%20menu%20click%20on%20%3CSTRONG%3Eformula%3C%2FSTRONG%3E%20--%26gt%3B%20under%20the%3CSTRONG%3E%20working%20with%20formula%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Emake%20sure%20the%20option%20%22%3CSTRONG%3EUse%20table%20names%20in%20formula%3C%2FSTRONG%3E%22%20is%20checked%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Untitled.png%22%20style%3D%22width%3A%20826px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F244637iC9B6A30439C02E65%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Untitled.png%22%20alt%3D%22Untitled.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20If%20my%20post%20helped%20you%2C%20please%20click%20on%20like.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2040557%22%20slang%3D%22en-US%22%3ERe%3A%20Structured%20References%20-%20How%20to%20Keep%20Them%20From%20Toggling%20Off%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2040557%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F87534%22%20target%3D%22_blank%22%3E%40Jihad%20Al-Jarady%3C%2FA%3E%26nbsp%3BI'm%20using%20excel%202010%2C%20which%20has%20different%20menus.%26nbsp%3B%20Unfortunately%2C%20this%20does%20not%20seem%20to%20be%20an%20option%20in%20this%20earlier%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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?

4 Replies

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

 

Untitled.png

 

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

@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.

@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

 

@Jihad Al-Jarady yes, it is within all of the tables on all of the worksheets within the workbook.