1

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.

RolandDW
  • 11
  • 1
  • 2
  • Why are you using `=Indirect(F1)` instead of `=Named_Range`? – Ricardo Diaz Feb 22 '20 at 20:05
  • Because F1 itself is a data validated drop-down list. If I select "Named_Range" in F1 then my cell containing =INDIRECT(F1) should return a dropdown list containing the items in "Named_Range". But I could select "Named_Range2" in F1 and then =INDIRECT(F1) should then return a different drop-down list. – RolandDW Feb 22 '20 at 20:16

1 Answers1

2

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.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • I tried creating Tables but that didn't work for me either. Is there another simple work around instead of using offset? – techscolasticus Aug 28 '20 at 01:46
  • @techscolasticus You haven't supplied enough information for me to even hazard a guess. I suggest you post a new question with the specifics of your problem, what you have tried, and your desired results. If you haven't has yet, take a look at the the HELP topics for [How do I Ask a Good Question](http://stackoverflow.com/help/how-to-ask), and also [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). It will help the respondents to help you best with your problem. – Ron Rosenfeld Aug 28 '20 at 02:01