List of Sheet Names

[MJCush]

Some years ago, I followed some advice and created a sheet that listed the other sheet names in the workbook using INDEX and REPLACE in the formulas. It worked perfectly. There was also advice that hitting Ctrl, Alt and F9 would recalculate the sheet. I think we were using version 2013 at the time.

Then I forgot about it. Now we are using version 2019. When I revisited the worksheet, all of the formulas have a #NAME? error. When I hit Ctrl, Alt and F9, it brings me to the login screen. I am guessing the problem is with this version of Excel.

Any advice?

[Fluff13]

What was the formula?

[MJCush]

I defined a Name, "Sheet_Names" as "=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") "

Then in my first sheet of the workbook, I had a series of formulas, using "=INDEX(Sheet_Names,A4) "

[Fluff13]

Both of those should still work. Is the workbook macro enabled?
Also in the trust center settings check the box for "Enable Excel4 Macros"

[Glenn Kennedy]

The standard way of doing this is to create a Named Range (called, e.g. S_List)

=MID(TRANSPOSE(GET.WORKBOOK(1))&T(NOW()),FIND("]",TRANSPOSE(GET.WORKBOOK(1))&T(NOW()))+1,255)

Set up from the Name Manager (CTRL-F3)

then

=S_List

The file MUST be saved as .xlsm to allow GET.WORKBOOK to function.

[MJCush]

The Macros were disabled. Thanks!!!

[MJCush]

Thank you. I will try that next time, but my code works now. Macros were disabled.

[Fluff13]

Glad to help & thanks for the feedback