I've been browsing through previous posts to find a way to show the tab name
of those several sheets to be displayed in a separate sheet.

Tabs named Test1, Test2 etc names to be displayed in say "Final" wksht in
cell a1 for Test1, cell a5 for Test2 etc.

Tried this but it kept displaying "Final" wksht tab name and variants of code.
=RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))
Any help be great.