CODEWITHSUNDEEP
exceldynamicexcel-indirect
RolandDW
RolandDW

Reputation: 11

So apparently the INDIRECT function doesn't work with data validation in Excel when using a dynamic named range

Trying to create a drop-down list in a cell where the source is a dynamic named range. The named range refers to:

=$A$2:INDEX($A:$A,COUNTA($A:$A))

Essentially the range starts at A2 and extends down to the last value in the column so that when I add or remove a value I don't end up with blanks in my list or the new values missing. I can use the following as the data validation source for my list with no problem:

=Named_Range

It provides me with a drop-down list of everything in the named range.

However, when I type the named range's name (Named_Range) into a cell (say F1) and use the following as the data validation source for my list, I don't get the drop-down list:

=INDIRECT(F1)

Searching around revealed that this is a know issue, but I can't get my head around a workaround.

Upvotes: 1

Views: 8142

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Instead of creating dynamic lists using the method you are using, I suggest using Tables.

Create a table for each dependent drop-down list. - Be sure to select "my table has headers"

Then

  • Select the data area of each Table (not the header)
  • In the Name box to the left of the formula bar, type in the appropriate Name
  • Hit Enter to confirm the name for the databodyrange.
  • Repeat for each table.

Since this is a table, the lists will autoadjust as you add/delete rows You can refer to them with the INDIRECT function as you set up your data validation list formula.

Upvotes: 2

Related Questions