Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Named Range | Excel Discussion (Misc queries) | |||
Trouble referring to a (dynamic) named range on another Excel shee | Excel Programming | |||
Dynamic Named Range | Excel Programming | |||
Trouble with dynamic named range | Excel Programming | |||
getting the absolute range address from a dynamic named range | Excel Programming |