Dynamic Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sandy - 16 Nov 2007 18:03 GMT

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

Reply to this Message

Dave Peterson - 16 Nov 2007 18:14 GMT

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)

> I have the following function defining the range "Grips". (The first value
> to be used in the "M" column is in Row 3.)
[quoted text clipped - 8 lines]
> Any ideas?
> Sandy

Signature

Dave Peterson

Reply to this Message

Don Guillett - 16 Nov 2007 18:15 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I have the following function defining the range "Grips". (The first value
>to be used in the "M" column is in Row 3.)
[quoted text clipped - 8 lines]
> Any ideas?
> Sandy

Reply to this Message

Sandy - 16 Nov 2007 18:34 GMT

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

>I have the following function defining the range "Grips". (The first value
>to be used in the "M" column is in Row 3.)
[quoted text clipped - 8 lines]
> Any ideas?
> Sandy

Reply to this Message

Rate this thread: