Array used as a Data Validation List

Is it possible to create an array that holds the names of all the visible worksheets in a workbook,
then use this array as a Data Validation List for a range of cells?

I know how to code a List box, to list all of the sheets,
but I’d rather have the user pick and choose from a drop down list in each cell.

After the user makes the selection, three other cells will be populated with data
from named ranges on each sheet.

I’ve include a workbook for illustration.

Thanks.

Your sample sheet is unclear if you want the PLATE sheet to simply LIST all the Recipe pages automatically, or if you want a data validation list in those cells that, when dropped open, would only list the sheets with recipes.

So I'm showing you both.

First, the named range called SheetsListed is a dynamic formula watching all the values in column A of the new sheet I added call SheetsList. Your macro NewRecipePage now has a line added that will add the name of any new recipes to the list on that sheet. You can hide this new sheet if you wish, to keep things looking tidy.

Next, I put a series of formulas in your PLATE sheet B4:B20. As it is now, any sheets listed on the SheetsListed column A will simply appear on the PLATE sheet as they are created.

If that's not what you wanted, but rather you wanted a drop down in those cells from which you could CHOOSE a sheet name from the list, that is there as well. Simply delete the formulas I put in those cells and use the drop downs instead.

Lastely, I put in formulas for columns C, D and E.