I want to create a named range (I'll call it GoalList) and I've tried to use
something like this:

=OFFSET(PP!Goal,1,0,COUNTA(PP!$C:$C)-1,1)

Unfortunately, there are blank values on column C, so I don't really get to
the last row in the range. I know how to do this with VBA, but I need a
named range. Can someone assist?

Thanks,
Barb Reinhardt

Hi, Barb

Try something like this:
=OFFSET(PP!Goal,1,0,LOOKUP(10^99,PP!$C:$C,ROW(PP!$ C:$C))-1,1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Barb Reinhardt" wrote:

I want to create a named range (I'll call it GoalList) and I've tried to use
something like this:

=OFFSET(PP!Goal,1,0,COUNTA(PP!$C:$C)-1,1)

Unfortunately, there are blank values on column C, so I don't really get to
the last row in the range. I know how to do this with VBA, but I need a
named range. Can someone assist?

Thanks,
Barb Reinhardt

I just noticed that you originally used COUNTA, so I'm inferring that you
could have either numbers or text in the referenced range.

In that case, try this:

=OFFSET(PP!Goal,1,0,MAX(LOOKUP(REPT("z",255),PP!$C :$C,ROW(PP!$C:$C)),LOOKUP(10^99,PP!$C:$C,ROW(PP!$C :$C)))-1,1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Hi, Barb

Try something like this:
=OFFSET(PP!Goal,1,0,LOOKUP(10^99,PP!$C:$C,ROW(PP!$ C:$C))-1,1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Barb Reinhardt" wrote:

I want to create a named range (I'll call it GoalList) and I've tried to use
something like this:

=OFFSET(PP!Goal,1,0,COUNTA(PP!$C:$C)-1,1)

Unfortunately, there are blank values on column C, so I don't really get to
the last row in the range. I know how to do this with VBA, but I need a
named range. Can someone assist?

Thanks,
Barb Reinhardt

No range is being defined when I enter this. Help me to understand how this
is supposed to work though.

Thanks,
Barb Reinhardt

"Ron Coderre" wrote:

Hi, Barb

Try something like this:
=OFFSET(PP!Goal,1,0,LOOKUP(10^99,PP!$C:$C,ROW(PP!$ C:$C))-1,1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Barb Reinhardt" wrote:

I want to create a named range (I'll call it GoalList) and I've tried to use
something like this:

=OFFSET(PP!Goal,1,0,COUNTA(PP!$C:$C)-1,1)

Unfortunately, there are blank values on column C, so I don't really get to
the last row in the range. I know how to do this with VBA, but I need a
named range. Can someone assist?

Thanks,
Barb Reinhardt

To account for all datatypes (numeric, text, logicals but not errors)
try:

=OFFSET(PP!Goal,1,0,LOOKUP(9**9,MATCH({"'",-9**99,FALSE},PP!
C:C,-1))-1,1)

On 9 May, 14:28, Barb Reinhardt
wrote:
I want to create a named range (I'll call it GoalList) and I've tried to use
something like this:

=OFFSET(PP!Goal,1,0,COUNTA(PP!$C:$C)-1,1)

Unfortunately, there are blank values on column C, so I don't really get to
the last row in the range. I know how to do this with VBA, but I need a
named range. Can someone assist?

Thanks,
Barb Reinhardt

Barb

InsertNameDefine.

Enter Ron's formula in the "refers to" dialog.

Give it a name like MyRange or Goallist.


Gord Dibben MS Excel MVP

On Wed, 9 May 2007 07:40:01 -0700, Barb Reinhardt
wrote:

No range is being defined when I enter this. Help me to understand how this
is supposed to work though.

Thanks,
Barb Reinhardt

"Ron Coderre" wrote:

Hi, Barb

Try something like this:
=OFFSET(PP!Goal,1,0,LOOKUP(10^99,PP!$C:$C,ROW(PP!$ C:$C))-1,1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Barb Reinhardt" wrote:

I want to create a named range (I'll call it GoalList) and I've tried to use
something like this:

=OFFSET(PP!Goal,1,0,COUNTA(PP!$C:$C)-1,1)

Unfortunately, there are blank values on column C, so I don't really get to
the last row in the range. I know how to do this with VBA, but I need a
named range. Can someone assist?

Thanks,
Barb Reinhardt