Specific Sheet Names Dropdown in Cell

[Vlr516]

I am trying to figure out how to create a dynamic dropdown of specific sheet names in a cell. Once a sheet name is selected, the data on the selected sheet will be used to populate the active worksheet. I would prefer not to use Data Validation.

• A user may create any number of sheets with beginning with the name "Requirements".
• On another sheet called "Upload", I want a cell with dropdown capability that lists all sheets beginning with the name "Requirements"
• The user will select one of the sheets from the dropdown list.
• The "Upload" sheet will refer to the selected "Requirements" sheet to grab all data to fulfill the formulas on the "Upload" sheet.

I have been searching all day on how to do this so any help is greatly appreciated.

Thank you.

 

[FDibbins]

Other than VBA, Data Validation is the only way to create your drop-down. What is your concern with using that?

The steps below will create a list of all sheet names, which can then be used in your DD, it requires that macros are activated because it uses a UDF (user defined function)

Create a range name (I called mine Sheetnanes
Refers to: =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

To get a list of sheet names, put this in a cell and copy down as needed (started in A2)...
=IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")

If you want to be able to click the cell and go to that worksheet, change that formula to this...
=IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")

If you need to add new sheets, you will need to recalc the sheet (F9) to include the new sheets

That named range can then be used for the DD

 

[Vlr516]

I haven't found a way for Data Validation to be as dynamic as I would like. I am creating a template which I won't have control over once given to the users. I would prefer a macro that evaluates the information at the point the Upload sheet needs created.

 

[FDibbins]

If you copy my suggested formula down past where you think you would need it (eg if you have 20 sheets, copy it down 50 cells), then a simple F9 will add any new sheets, adjust any changed sheet names, or exclude any deleted sheets

 

[Vlr516]

Other than VBA, Data Validation is the only way to create your drop-down. What is your concern with using that?

To get a list of sheet names, put this in a cell and copy down as needed (started in A2)...
=IFERROR(INDEX(Sheetnames,ROWS($A$2:A2)),"")

If you want to be able to click the cell and go to that worksheet, change that formula to this...
=IFERROR(HYPERLINK("#"&"'"&INDEX(Sheetnames,ROWS($A$2:A2))&"'!A1",INDEX(Sheetnames,ROWS($A$2:A2))),"")


That named range can then be used for the DD

Is there a way to get the specific sheets that begin with "Requirements" in a dropdown within a cell or combobox instead of listing them individually on several rows?

 

[FDibbins]

Once you have that list of sheets, you could create your extract list like this...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]Full List[/td][td][/td][td]Extract[/td][/tr]

[tr][td]

2​

[/td][td]Requirements1[/td][td][/td][td]Requirements1[/td][/tr]

[tr][td]

3​

[/td][td]requirements2[/td][td][/td][td]requirements2[/td][/tr]

[tr][td]

4​

[/td][td]Requirements3[/td][td][/td][td]Requirements3[/td][/tr]

[tr][td]

5​

[/td][td]Requirements4[/td][td][/td][td]Requirements4[/td][/tr]

[tr][td]

6​

[/td][td]test1[/td][td][/td][td]Requirements10[/td][/tr]

[tr][td]

7​

[/td][td]test2[/td][td][/td][td]Requirements11[/td][/tr]

[tr][td]

8​

[/td][td]test3[/td][td][/td][td]Requirements12[/td][/tr]

[tr][td]

9​

[/td][td]test4[/td][td][/td][td]Requirements13[/td][/tr]

[tr][td]

10​

[/td][td]test5[/td][td][/td][td]Requirements14[/td][/tr]

[tr][td]

11​

[/td][td]Requirements10[/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]Requirements11[/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]Requirements12[/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]Requirements13[/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]Requirements14[/td][td][/td][td][/td][/tr]
[/table]
C2=IFERROR(INDEX(A:A,SMALL(IF(LEFT($A$2:$A$22,4)="Requ",ROW($A$2:$A$22)),ROWS($A$1:A1))),"")
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.

Then copy down as needed