Trouble with dynamic named range
 

In an Excel 2010 workbook I created a dynamic named range from the Formula
tab using the Define Name utility. The formula I entered is:

=OFFSET(Price Groups!$AP$2,0,0,COUNTA(Price Groups!$AP:$AP)-1,1)

I want the range to include all non-empty cells in the AP column starting
with the second row. When I check the named range using the Name Manager it
shows the formula:

=OFFSET(Price Groups!$AP$2,0,0,COUNTA(Price Groups!$AP$1:$AP$65536)-1,1)

I'm not sure why Excel converts my formula, but I've tried several time to
change it back to my formula and each time I get the same result. If anyone
know what I'm doing wrong here, I'd appreciate any offered advice.

TIA,

Ken

Trouble with dynamic named range
 

Try PriceGroups as ONE word

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ken Warthen" wrote in message
...
In an Excel 2010 workbook I created a dynamic named range from the Formula
tab using the Define Name utility. The formula I entered is:

=OFFSET(Price Groups!$AP$2,0,0,COUNTA(Price Groups!$AP:$AP)-1,1)

I want the range to include all non-empty cells in the AP column starting
with the second row. When I check the named range using the Name Manager
it
shows the formula:

=OFFSET(Price Groups!$AP$2,0,0,COUNTA(Price Groups!$AP$1:$AP$65536)-1,1)

I'm not sure why Excel converts my formula, but I've tried several time to
change it back to my formula and each time I get the same result. If
anyone
know what I'm doing wrong here, I'd appreciate any offered advice.

TIA,

Ken

Trouble with dynamic named range
 

Don,

Thanks for the advice, but the worksheet name is Price Groups (with a space
between Price and Groups). The worksheet name is used througout the project
so it's not something I can easily change.

Ken

"Don Guillett" wrote:

Try PriceGroups as ONE word

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ken Warthen" wrote in message
...
In an Excel 2010 workbook I created a dynamic named range from the Formula
tab using the Define Name utility. The formula I entered is:

=OFFSET(Price Groups!$AP$2,0,0,COUNTA(Price Groups!$AP:$AP)-1,1)

I want the range to include all non-empty cells in the AP column starting
with the second row. When I check the named range using the Name Manager
it
shows the formula:

=OFFSET(Price Groups!$AP$2,0,0,COUNTA(Price Groups!$AP$1:$AP$65536)-1,1)

I'm not sure why Excel converts my formula, but I've tried several time to
change it back to my formula and each time I get the same result. If
anyone
know what I'm doing wrong here, I'd appreciate any offered advice.

TIA,

Ken

.

Trouble with dynamic named range
 

Hi
Try 'Price Groups'

regards
Paul

On Jun 7, 11:47*pm, Ken Warthen
wrote:
Don,

Thanks for the advice, but the worksheet name is Price Groups (with a space
between Price and Groups). *The worksheet name is used througout the project
so it's not something I can easily change.

Ken



"Don Guillett" wrote:
*Try PriceGroups as ONE word

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ken Warthen" wrote in message
...
In an Excel 2010 workbook I created a dynamic named range from the Formula
tab using the Define Name utility. *The formula I entered is:

=OFFSET(Price Groups!$AP$2,0,0,COUNTA(Price Groups!$AP:$AP)-1,1)

I want the range to include all non-empty cells in the AP column starting
with the second row. *When I check the named range using the Name Manager
it
shows the formula:

=OFFSET(Price Groups!$AP$2,0,0,COUNTA(Price Groups!$AP$1:$AP$65536)-1,1)

I'm not sure why Excel converts my formula, but I've tried several time to
change it back to my formula and each time I get the same result. *If
anyone
know what I'm doing wrong here, I'd appreciate any offered advice.

TIA,

Ken

.- Hide quoted text -

- Show quoted text -

Trouble with dynamic named range
 

I haven't been following this thread so this may have been said, but in
certain situations, Excel has trouble with spaces in sheet names unless
they have single quotes around them,

You may be beyond this now, but I usually code something like:
Public Const PriceGroups as String = "Price Groups"
and always use the symbol rather than the literal string. That way, it is
easy to change the sheet's name.

Could you do a search & replace searching for "Price Groups" with the
quotes?

On Tue, 8 Jun 2010 06:37:48 -0700 (PDT), Paul Robinson
wrote:

Hi
Try 'Price Groups'

regards
Paul

On Jun 7, 11:47*pm, Ken Warthen
wrote:
Don,

Thanks for the advice, but the worksheet name is Price Groups (with a space
between Price and Groups). *The worksheet name is used througout the project
so it's not something I can easily change.

Ken



"Don Guillett" wrote:
*Try PriceGroups as ONE word

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Ken Warthen" wrote in message
...
In an Excel 2010 workbook I created a dynamic named range from the Formula
tab using the Define Name utility. *The formula I entered is:

=OFFSET(Price Groups!$AP$2,0,0,COUNTA(Price Groups!$AP:$AP)-1,1)

I want the range to include all non-empty cells in the AP column starting
with the second row. *When I check the named range using the Name Manager
it
shows the formula:

=OFFSET(Price Groups!$AP$2,0,0,COUNTA(Price Groups!$AP$1:$AP$65536)-1,1)

I'm not sure why Excel converts my formula, but I've tried several time to
change it back to my formula and each time I get the same result. *If
anyone
know what I'm doing wrong here, I'd appreciate any offered advice.

TIA,

Ken

.- Hide quoted text -

- Show quoted text -