Data Validation: How to create a dynamic range in column A and use itfor a drop-down list

  • Thread starter Thread starter Mike C
  • Start date Start date
M

Mike C

I am trying to define a dynamic range on sheet 3, which I want to be
able to use as a dropdown list as a part of the data validation "list"
option.

But whenever I select the drop down list after creating the validation
rule (via data > validation), I cannot see my list.

Perhaps I am using the wrong formula for the dynamic range?

Here is what I am doing.

List is in Sheet 3, rows 1-10 (and growing).

Defined name is: =OFFSET('Sheet3!A1,0,0,COUNTA('Sheet3!!$A:$A))

In sheet 1, column C, I am selecting the column, then selecting data,
validation, list (and placing "=nameofmylist" in the source box.

Thanks for any thoughts......
 
Hi Mike

Name the range which containing your validation list (including some
empty rows) - via "Insert > Name"

Then set the source (Data> Validation> List>) "=NamedRange" and make
sure that the checkbox "Ignore blank cells" is checked.

Using named ranges is the only way to refer to validation lists on
other sheets.

Regards,

Per
 
Use an absolute reference ($A$1) to the starting cell in the Offset
formula, instead of a relative reference (A1):

=OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet3!$A:$A))
 
Back
Top