Dynamic Range

[Sandy]

I have the following function defining the range "Grips". (The first value
to be used in the "M" column is in Row 3.)

=OFFSET(Jobs!$M$3,0,0,COUNTA(Jobs!$M:$M),1)

I then use the name "Grips" as a validation list "Source".

It works fine other than I get all the values plus a blank space at the
bottom of my drop-down list.

Any ideas?
Sandy

 

[Dave Peterson]

What's in M1:M2?

Remember your =counta($m:$m) is including those cells, too.

I'd put something in both cells. If I want to make it look like that cell is
blank, I'll use a formula: =""

Then you could use:
=OFFSET(Jobs!$M$3,0,0,COUNTA(Jobs!$M:$M)-2,1)

Or if you don't want worry, you could subtract the number of used cells in
M1:M2:

=OFFSET(Jobs!$M$3,0,0,COUNTA(Jobs!$M:$M)-counta($m$1:$m$2),1)

 

[Don Guillett]

=OFFSET(Jobs!$M$3,0,0,COUNTA(Jobs!$M:$M)-3,1)
or
=OFFSET(Jobs!$M$3,0,0,COUNTA(Jobs!$M:$M)-1,1)

 

[Sandy]

I have a heading in M1 (and a few other columns too) because I wanted to be
able to easily sort the values in these columns for easier reading in the
drop-down.
M2 is blank in order to not allow heading to become part of Ascending sort.

So

=OFFSET(Jobs!$M$3,0,0,COUNTA(Jobs!$M:$M)-1,1)

works a treat.

Thank you both
Sandy