Trouble with dynamic named range

[Ken Warthen]

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

 

[Don Guillett]

Try PriceGroups as ONE word

 

[Ken Warthen]

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

 

[Paul Robinson]

Hi
Try 'Price Groups'

regards
Paul

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







- Show quoted text -

 

[Guest]

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?