How to create a dynamic dropdown of sheet names?

I need to create a dynamic dropdown of sheet names without using VBA or Excel 4.0 macros.

I want to have a dropdown of sheet names which updates any time someone adds a sheet into the relevant section of the workbook, or changes the name of any of these sheets. The new sheets are only added in by copying a template sheet.

I can't use Excel 4.0 macros because Trust Centre is resetting so as not to allow them, every time a new person opens the shared workbook, and I don't want my client to have to keep resetting Trust Centre. I couldn't find a solution searching the forums that didn't use VBA or 4.0 macros.

I tried the formula =SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")

This worked fine as long as Excel 4.0 macros were enabled but Trust Centre kept blocking them every time a new person opened the shared workbook.

I found an answer to this that works in Microsoft 365.

I used this formula from elsewhere on the forum in cell B1 on all the sheets in the range so that each sheet would have its own name on it in the same location:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Then on a separate sheet I used this formula to create the dropdown list for data validation:

=TEXTSPLIT(TEXTJOIN(", ",TRUE,'StartSheet:EndSheet'!$B$1),,", ",TRUE,0)