Defining Named Range for Lastrow in a specific column
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 |
Defining Named Range for Lastrow in a specific column
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 |
Defining Named Range for Lastrow in a specific column
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 |
Defining Named Range for Lastrow in a specific column
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 |
Defining Named Range for Lastrow in a specific column
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 |
Defining Named Range for Lastrow in a specific column
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 |
All times are GMT +1. The time now is 11:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com