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
0
Utf
6/7/2010 5:34:35 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
2340 Views

Similar Articles

[PageSpeed] 45

 Try PriceGroups as ONE word

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Ken Warthen" <KenWarthen@discussions.microsoft.com> wrote in message 
news:6B6E3EEA-6A70-436C-81E6-5EF34BE70B75@microsoft.com...
> 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 

0
Don
6/7/2010 9:03:19 PM
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
> dguillett@gmail.com
> "Ken Warthen" <KenWarthen@discussions.microsoft.com> wrote in message 
> news:6B6E3EEA-6A70-436C-81E6-5EF34BE70B75@microsoft.com...
> > 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 
> 
> .
> 
0
Utf
6/7/2010 10:47:07 PM
Reply:

Similar Artilces: