Automatic update of excel tab names - unlocked
Originally published: 11/06/2020 15:23
Publication number: ELQ-96709-1
View all versions & Certificate

Automatic update of excel tab names - unlocked

This is a useful tool for the advanced and expert excel user to get an automatic update of a tab name change in a cell

In complex business plans, excel models and spreadsheets there are certain situations where changing a tab name could cause formulas to give errors, mess up calculations and in complex spreadsheets these would be hard to track down and correct leading to mistakes in models.
This is a simple example for the advanced excel user demonstrating how to tackle this problem which typically occurs alongside the use of INDIRECT function. INDIRECT function offers extreme benefits and flexibility but could also cause errors if not applied correctly. The example focuses only on tab name changes when using INDIRECT function to call on cells in those tabs.
This is achieved without the use of complex Macros in excel. The address of each cell which belongs to a specific Tab is recorded to the CELL data. Using some text functions, it is possible to isolate the cell name and identify the name of the Tab the cell belongs to and hence use this formula in an INDIRECT function so that in case of a change in the tab name, the INDIRECT formula could always be up to date with the latest tab name automatically and provide the correct cell call. This avoids #REF and errors

This Best Practice includes
1 Excel example

Aram K offers you this Best Practice for free!

download for free

Add to bookmarks


5.0 / 5 (1 votes)

please wait...