Dynamic Dropdown list of sheet names

[rsulliva]

Hi guys, I would like to create a dynamic dropdown list in excel of all the tab names (and if possible, only the visible tabs). As tabs are added to the workbook, the dropdown list should be able to automatically add these names to the dropdown list.

Context: I am currently using the formula below to return only the current tab name into a cell to be able to pull its name into a second formula. I would like to use a dropdown list to be able to toggle the first cell that the second formula is using in order to select the tab from which the second formula is pulling its data. So, if a user wants to pull data from "week 7", they use the dropdown to change the cell to "week 7" thereby driving all other formulas on the page to reference week 7's worksheet using an "Indirect" formula built into them. Is there a way to create a dynamic dropdown?

=TEXTAFTER(CELL("filename", A1), "]")

Thanks,

Russell

 

[Armstrong_N14]

Hope this Helps:

Microsoft Excel: Create an automated list of worksheet names

How to create an automated list of worksheet names in Excel -- and add a table of contents.

www.journalofaccountancy.com

 

[rsulliva]

Thanks Amstrong_N14. I should be able to make the vba in the link provided work for me.

 

[alansidman]

An alternative with Power Query

let
    Source = Excel.Workbook(File.Contents("YourPath/YourFileName"), null, true),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Data"})
in
    #"Removed Columns"