Why won't my worksheet sheet name list update when a worksheet name is changed or deleted?

Question

In Excel 2010, I have a workbook with a column that identifies the names of each worksheet in the workbook. The following formula is used for this: =INDEX(SheetList,ROW()) where SheetList is a named reference to the following formula: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1))," ").

The list updates when a new sheet is added, but does not update when a sheet name is changed or when a sheet is deleted. The only way I have found to update the list is to copy and paste the formula back into all the cells of the column. Any ideas as to why it doesn't update automatically, what can be done to make it update automatically, or perhaps a hot key command to make the formulas update?

Answer 1

If it can be reasonably assumed that 'the list' starts in C2 and fills down from there then right-click the worksheet's name tab and chose View Code. When the VBE opens, paste the following into the code sheet titled something like Book1 - Sheet1 (Code).

Private Sub Worksheet_Activate()
    Range(Cells(2, 3), Cells(Rows.Count, 3).End(xlUp)).FillDown
End Sub

Whenever you return to (aka activate) the worksheet containing the worksheet list, the list will be updated.

Answer 2

• To calculate it using hot key command : CTRL + ALT + SHIFT + F9

• To make it update automatically we can add to it a Volatile Function that return empty string like TODAY() :

  =INDEX(SheetList,ROW())&IF(TODAY()=TODAY(),"")

this worked for me and updated automatically when delete or rename a sheet but not worked when add new sheet (but it updated by pressing F9).