Microsoft Excel: Dynamic Data Validation List with Indirect References

0
votes

I'm looking to create a data validation list that can dynamically reference a sheet and dynamically populate only the cells that have data in them.

I can make a dynamic data validation list that references an non-dynamic sheet using this formula: =OFFSET(SHEET_NAME!$A$2,,,COUNTA(SHEET_NAME!$A:$A))

And I can make a non-dynamic data validation list (Only A2:A25) that dynamically references any sheet where Cell A3 contains the sheet name using this formula: "=INDIRECT("'"&A3&"'!$A2:$A25")"

But I have been unable to combine the two to make a dynamic data validation list that can dynamically reference sheets. If anyone has any advice to make the two work together or a better method I would greatly appreciate it.

Thanks

1
excel

1 Answers

0
votes

Assuming you have three worksheets where:

  • On worksheet A, you have ListA with number 1 to 4 in range A2:A5
  • On worksheet B, you have ListB with a,b,c,d,e in range A2:A6
  • On worksheet Result, in cell A1 you will input the target sheet name (either A or B), in cell A3 you will use data validation to display a dynamic drop-down list.
  • name the dynamic range as DynamicList

The formula you will use for the named range DynamicList is

=INDIRECT(Result!$A$1&"!A2:A"&COUNTA(INDIRECT(Result!$A$1&"!A:A")))

When setting up the data validation in cell A3 on the Result sheet, you will enter the following: screenshot - data validation

Then you should be able to change the sheet name in cell A1 on the Result sheet to get a dynamic drop-down list as shown below:

screenshot - dynamic list