Defined Formula to List Sheet name refresh issue

[gtarget]

Hi All,

Please see attached for a worked example. I would like to create a worksheet in which I have a Summary page that lists all the sheet names in a table (which can then be used to pull data from the individual sheets).

I've used the following defined name to list the sheets in the table and then the index formula (as seen in column C of the Summary Sheet)

ListSheets=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

The issue is that if I add more sheets (say you were to copy sheet 2C and rename it as 2D), then the table at the front doesn't refresh to fill out the new name, and then the indirect formulas in columns D to H of the summary sheet come up with reference errors.

The solution so far is that everytime I add a sheet I have to drag down the index formula in column C to refresh the column and then it works. THis is not very elegant - and the worksheet needs to be foolproofed for other people. Is there

a) a way to do this with the above List Sheets formula
b) if not a small macro that i could add with say a "REFRESH" button that refreshed all instances of the "ListSheet" formula

Thanks in advance for your help.

Regards,

George