I have an Excel Workbook consisting of these sheets:
Products, Brands, Projects, Overview
In the sheet Overview in Range A1:A4 I want to list the names of these sheets.
Therefore, in the Name Manager I inserted the following:
List_of_Workbook = REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")
Afterwards in Cell A1 I inserted this formula:
=TRANSPOSE(INDEX(List_of_Workbooks,))
The result looks like this:
All this works perfectly.
However, there are two issues with this solution:
- I need to enable VBA Excel 4.0 macros.
- In case I add a new sheet I have to click into
Cell A1again to refresh the list.
Is there solution which does not need VBA Excel 4.0 and refreshes the list automatically when a new sheet is added?
1