Tab name list formula quite working with latest Excel update

[kualjo]

I've been using a formula that incorporates a named range in order to create a list of tabs in a dropdown. With the latest update of Excel, it has stopped working. I'm wondering if there something in the new version that would have disabled either the formula itself or the way the named range feeds the formula. The cell formula itself is:

=IFERROR(@INDEX(Sheets,ROW()),"-")

The named range formula (named Sheets) is:

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")

The only change I can see is the addition of the @ symbol in the formula, which I know is a new Excel constant. If I remove it, I still return an error.

Can either of these be edited to return to the previous functionality? I can do a macro, but I'd prefer not to; the formulas have worked just fine until now and I'd rather stay with them..

Thanks.

 

[Fluff]

If you put this in a cell (&clear all cells below) what do you get

=TRANSPOSE(Sheets)

 

[Dave Patton]

I do not use the Named Formula that follows but I built it to see if it works,
I have the current version of 365.

File Name.xlsm
	A	B	C
1	1	1a
2	2	1b
3	3	1c
4	4	1d
5	5	1e
6	6	1f
7
8
9	Named formula	ListSheets	=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
10
1a

Cell Formulas
Range	Formula
B1:B6	B1	=INDEX(ListSheets,A1)

 

[kualjo]

Fluff, that looks like a remarkably simple solution, but I think the spill is going to create an issue due to other elements of my worksheet. Not sure if I can work around it or not.
Dave, it took me a minute to realize you had modified the named formula, but it seems to work like it did before. Looks like that might be my answer.
Thanks all!

 

[Dave Patton]

You may want to use VBA.
Excel 4 Macros are fairly old; Excel version 4 or earlier!
Before Visual Basic for Applications, there were Excel macros, or XLM. VBA was introduced in Excel version 5.0 so these older XLM macros are also known as Excel 4 Macros.

 

[Fluff]

Fluff, that looks like a remarkably simple solution, but I think the spill is going to create an issue due to other elements of my worksheet.

Having it spill can make things simpler for instance if the formula is in A2 you can simply use

=A2#

in the data validation, rather than needing named ranges.

 

[kualjo]

The TRANSPOSE function initially gave me a #BLOCKED error. It said that was an old 4.0 macro and wouldn't work. Then I discovered that in the Macro Settings in the Trust Center, there is now a checkbox to allow those old macros to run. I checked it, tried again, and voila, problem solved! Thanks!

 

[Fluff]

Glad you sorted it & thanks for the feedback.