Dropdown With Current sheets in document

[jlara0687]

Hi,

I'm Trying to create a dropdown menu in excel that has the current sheets on the bottom of the document, done in a way that if sheets are added or removed they will auto populate in the dropdown. I've seen it done with PowerQuery briefly but cannot find how to accomplish this task. I've looked online and all i get is VBA scripts. Any assistance would be much appreciated.

Than You in Advance

-jlara0687

[dflak]

Is there a particular reason you don't want a VB solution?

[jlara0687]

Thank you for your reply. Not really, the only reason is I had seen it accomplished with PowerQuery and I wanted to know how it was done. A VBA solution is definitively welcome. The VBAs I found online were not working correctly, plus I am not well versed in them so I wouldn't know where to begin editing them.

Thank you

[dflak]

I think this will work for you since it is a "fill in the blanks" kind of thing. I set it up so that you can create and maintain the drop down list on as many sheets in as many cells as you would like. All you have to do is pass the name of the sheet and the address of the cell to the macro CreateList. In the example below, the macro, MakeDropDown, shows how to do this. It puts a validation drop-down on the Control Panel Sheet in cell C1 and on sheet New Sheet in cell A1. Modify this macro to reflect the sheets and cells you want.

Please Login or Register  to view this content.

The following code can be added to the ThisWorkbook module

Please Login or Register  to view this content.

When you add a new sheet, it will add it to the list. However, I cannot find a good solution to detect when you rename or delete a sheet. There is an "event" for BeforeSheetDelete, but it runs all the code before deleting the sheet, so it does no good.

It is probably best to map the MakeDropDown macro to a control key such as CTRL-L (for list).

Attached is the workbook I used to develop this. Have fun.

[jlara0687]

Thank You. Much appreciated, I've tried using the VBA and it worked like a charm. How different would it be if I was trying to instead of using data from worksheets within the same workbook, data from other workbooks with only one worksheet by selecting this data through a drop-down? in the following example, there is one workbook called File Dashboard in a folder on my desktop and 2 workbooks called sample_1 and sample_2. in the "dashboard" workbook cell A1 references to A1 in Sample_1 and Sample_2. ie. "=[Sample_1.xlsx]Sheet1!A1"I've tried having Sample_1 from "=[Sample_1.xlsx]Sheet1!A1" replaced with a cell that has a dropdown to replace it and have the value change as I change the drop-down, unfortunately, all I get is an error. I tried using the "indirect" function but I'm not really getting anywhere. any assistance would be greatly appreciated.

[Zer0Cool]

Here you are, using formula and a named range, no code required (though technically still a macro). See my attached example. Using this method files still need to be saved as macro enabled despite no code being entered into a VBA project

As long as you fill the formula beyond the number of rows equal to the number of sheets you have, new sheets will automatically appear in the list as soon as they are added (or will disappear when removed).

Simply make a data validation drop down list based on the list generated by this formula and you can have a drop down list of sheets any place in the file you want.