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

[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......

 

[Per Jessen]

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

 

[Debra Dalgleish]

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))