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
2278 Views

Similar Articles

[PageSpeed] 1

 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:

Formula to capture worksheet names #2
this is Exactly what I need. I appreciate ya bro -- LowKe ----------------------------------------------------------------------- LowKey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1527 View this thread: http://www.excelforum.com/showthread.php?threadid=26897 No problem. Glad it helped. :) Take care -- firefyt ----------------------------------------------------------------------- firefytr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=664 View this thread: http://www.excelforum.com/showthread.php?threadid=26897 ...

Exchange 2000
Hi all I've recently renamed a user. Now I found that everytime I type that user name in the outlook, it shows me the name like this NewUserName <OldUserName>. I've checked all in the AD, but not found anythings related to the old user. How to correct it? Please help. Thanks BT Just a few points ... 1 - you can delete the names from the autocomplete by highlighting them (use the keyboards arrow keys) and hitting the DEL key. 2 - the <OldUserName> is displayed because that is the user's LegacyDN value. This value can be changed by using adsiedit however if...

why are my excel file name colored blue ?
Why are some of my excel files colored blue and some are in black? This occurred after an IT tech servicing was done> "valbwoy" <valbwoy@discussions.microsoft.com> wrote in message news:5A643AA1-3971-4498-A24D-2C569D330225@microsoft.com... > Why are some of my excel files colored blue and some are in black? This > occurred after an IT tech servicing was done> If this is when you are viewing the files in Windows Explorer, the blue may well mean that the relevant files are compressed. There is an option in Control Panel that allows compressed files to be shown ...

Printing Multiple Ranges
I have a worksheet that has 16 different ranges (entitled Page1, Page2, ..., Page16). I want to print each page in its correct order. So I go to Page Setup and make the print area be "Page1, Page2, Page3" and so on. But instead of keeping the ranges in that order, it converts them to cells so the print_area becomes J139:ER193. This wouldn't be a problem, except that the pages aren't in the correct order in the worksheet, so when I print I get page 6, 7, 8, 13, 1.... So I ask, is there a simple way to do this in excel? I also tried to write a macro to do this, a...

permute contents of a range
hi guys i have a range of 16 c ells, say a1-a16 and they contain numbers 1 to 16, in this order. i want to creat several ranges b1-b16, c1-c16 etc. that would contain the same numbers but in a random order. how can i do this? thank you dragos Hi, B1, copied across and down: =RANDBETWEEN(MIN($A$1:$A$16),MAX($A$1:$A$16)) You'll need to make sure that you have the Analysis ToolPak add-in installed. Hope this helps! In article <20358BAB-AAEA-42FE-97F2-4B7026AE384C@microsoft.com>, "draggavv" <draggavv@discussions.microsoft.com> wrote: > hi guys > > ...

HELP If range equals
When Department changes I need to insert the ADDRESS below the Account-Institution Business Office. For instance if department = 883, go to Account-Instittion business office and if = blank get address abc 123 Company 500 Thompson Rd. Detroit, MI ending 02/13/10 ---------------------------------------------------------------- Account-Institution Business Office: DEPT EMP.NBR EMPLOYEE NAME ADDRESS 883 27903 ROBERSON, JOE abc 883 119245 MUSIL, TODD E ...

List output to a x by 10 range
I have a list that x lines long. I want to transpose that list to another range (starting on cell M1) that is 10 columns wide and however many rows long. I know this is super simple. Does it have something to do with range resizing? No doubt you've already experimented with macro recorder, discovering the standard transpose command wont wrap within a range. So we need handle the transposition manually. Presuming your data begins in cell A2: Sub test() For Each c In Range("A2", "A" & Range("A65536").End(xlUp).Row) Row = Row + 1 ...

Sort name from specified interval table
Hiiii I am facing a silly problem i think u guys are rescue me from that... My prob like I have two cloum one colum contain Name of person and another coloum contain their salary.I prepared a interval table contain 4 row according to salary range....I have find the name whos salary has on the specified range on ist row of interval table.... Can any one giv the idea about that..Perhaps I tried it VLook up Function...But i faced proble to sort the name from ist colum -- mun04 ------------------------------------------------------------------------ mun04's Profile: http://www.excelforum....

Defining a name
Whats up everyone. Can I have a table array that is dynamic based on a input sheet. For example I have a lookup table that =VLOOKUP(Reference!$E6,CompanyDataQ3,Reference!$F6,FALSE) I would like CompanyDataQ3 to change based on the input sheet. On the input sheet I have Quarter so if quarter on the input sheet is Q3 the lookup table will have =VLOOKUP(Reference!$E6,CompanyDataQ3,Reference!$F6,FALSE) if the quarter on the input sheet is Q4 than the lookup table will hav =VLOOKUP(Reference!$E6,CompanyDataQ4,Reference!$F6,FALSE) Please let me know if this is possible and how to best a...

in access databse the function date() returns #name
i have created an access data base at access 2003 profisonal and used the function date() it worked properly . but when i have coped my data base at another computer at the same office , the funtion retuned (name#) aW4gQWNjZXNzOyB0aGUgRGF0ZSBmdW5jdGlvbiBkb2Vzbid0IHdvcmsgc29tZXRpbWVzLgoKaXQgd29ya3MgYWxsIHRoZSB0aW1lIGluIFNRTCBTZXJ2ZXI7IGJ1dCB5b3UndmUgZ290IHRvIGNhbGwgaXQKR0VUREFURSgpCgoKCk9uIE1hciA3LCAxMToyNMKgcG0sINmF2K3ZhdivINiq2KfYrCDYp9mE2KPYtdmB2YrYp9ihINin2YTYqNiu2YrYqiDZhdit2YXYryDYp9mE2LPZitivCjxAZGlzY3Vzc2lvbnMubWljcm9zb2Z0LmNvbT4gd3JvdGU6Cj4gaSBoYXZlIGNyZWF0ZWQgYW4gYWNjZXNzIGRhdGEgYmFzZ...

Outlook 2007, Cached Mode, name resolution fails
I'm having a similar problem as discussed here: http://groups.google.com/group/microsoft.public.outlook/browse_thread/thread/1f521281a6a07fa6/01bdb551c91c034d?lnk=gst&q=cached+mode&rnum=16#01bdb551c91c034d With Outlook 2007 the key for Cached Mode no longer exists. I'm also receiving the following error when I do a check names when I type portions of users display names: "Too many names have been found in the directory service or the server has exceeded its time limit for searching. Type more letteres of the name and try again." I've checked and our OAL is bei...

AutoFilter method of Range class failed
Hello, I'm trying to automate some formatting of an Excel 2007 workbook using VB.NET in Visual Studio for Applications to run as a Script Task in SqlServer Integration Services. I have a fairly simple range, and I'm able to do everything I want with it, except for Autofilter. I've tried various methods for specifying the range (explicit, A1, UsedRange, Columns, etc.) No matter what I do, I keep getting an exception with the message of "AutoFilter method of Range Class failed". I've declared variables for the application, workbook, worksheet, and r...

Need trailing zeroes to show for range of numbers....
The file I pulled into Excel for some reason cut off 2 trailing zeroes, no matter how I format it they will not show up. There are thousands of them and I can't see typing in each one over just to get two zeroes on the end. Does anyone know of a way to format this so they will show up? I have searched Microsoft online with no help. Not too much information, but try this (after saving your workbook!) In an empty cell enter the numner 100. Edit>Copy Select your data Edit>Paste Special, check Multiply -- Kind Regards, Niek Otten Microsoft MVP - Excel "Sueshe" &...

Dynamics Great Plains v10 trial version
I just recently downloaded (from the MS website) the 90-day trial version of GP v10 to my personal computer. The install appeared to go well however, while the login is "SA" I do not know the password. I have tried "password" and tried leaving it blank. Anyone know the answer? When I called MS Business Solutions, they told me that it will ask me to set up a password during the install, it did not. Thank you Did you try 'sa' as the password? -- Bud Cool, Accounting System Manager HDA, Inc. Hazelwood, MO GP 9.0, SP2 "Pam" wrote: > I just...

count the number of new lows within a range of cells
Hello, Is there a formula or method to count the number of new lows within a range of cells? For example, the following are in col A rows 5 to 14. Within this set a new low has been reached 3 times- (96,95,94). What I would like is a formula or macro that that would produce the results - 3. There are about 9000 rows of data and I am looking for "count of new lows within the preceding 10 rows" on a rolling basis. Thanks. 100 101 96 97 98 97 95 97 94 95 Version 1: Use this if the 9000 or so rows don't have blanks in them. In this version you just need to select the first cel...

Trouble with schedule mail sending
I am using Outlook 2000 with Exchange 2003 Enterprise SP1. Our users are on Outlook 2000, XP, and 2003 clients. I have found an issue when sending mails scheduled for a particular time. In all versions of Outlook you can schedule a message to be delivered at a particular time by configuring the message options where you will find a field "Do not deliver before" with a drop down box to select a time. In Outlook2000 I also found that you can set a send delay on a message by clicking FILE can choosing PROPERTIES to modify the message properties. There you can specify a delay in # of...

Trouble installing business contact manager
I get a message stating "unable to configure server" when I try to install the business contact manager to my Outlook account. I can't find any help on the microsoft website, but this must be pretty simple for someone who knows how these programs work with each other. The Outlook installation is working fine, although I had to redo it after a Sasser episode. My guess is that some setting on the Outlook account has to be changed before the BCM can load. Thanks in advance for any help. Walter 1. Are you running an Exchange Account? 2. Do you have Administrative rights...

Sales Tax Issue with Dynamics RMS
Here in California, the sales tax recently increase from 8.25% to 9.25%. I made this appropriate changes to the sales tax settings in RMS. The issue is when I get a return for items purchased prior to the sales tax change, the customer is credited at the current sales tax rate of 9.25% rather than the 8.25% that they should be credited. I need to be able to adjust the sales tax percentage at the POS for these returns so that the sales tax amount is correct. Any suggestions? Paul: How about setting up an "Old Tax" code at 8.25% and using Shift-F12 to select and apply the old tax...

VSTO
I retrieve a PivotTable in Analysis Services. How can i get the Pivotitems in spreadsheet to hide/unhide? I maked a loop in the pivottable, but the interop disable th pivotitems -- zeri ----------------------------------------------------------------------- zeric's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3100 View this thread: http://www.excelforum.com/showthread.php?threadid=50675 ...

List Box Troubles
I have a form that I am trying to create. Within this form, one of the things you choose is a product ID. Once that is chosen, I would like to have my listbox refreshed with all of the warehouses that the product is available in. I first attempted to create a temp table on the AfterUpdate event of my ProdID label. I receive an error stating the previous temp table can't be deleted because it is in use. I would use the control source in properties, but I don't know how to "pass" the prodID variable like that. It has been a little while since I have worked in Ac...

Shared Calendars not Displaying User Name
Outlook 2003 / Exchange 2003, both fully patched and running on XP Pro SP2. Shared calendars added under "Open a shared calendar". Header of calendars only displays "Calendar", not the username of the shared calendar owner. How can I get Outlook to display the username, please. At the moment I have multiple calendars open and they all are headed "Calendar". Not very useful :-( TIA, Bill http://www.outlook-tips.net/archives/2005/20050809.htm -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for W...

dynamic menu
Does anybody have any sample on how to build menu dinamically using xml files? Dan has some on his site. http://www.xmlforasp.net/search.aspx?searchText=menu This one is useful. http://www.xmlforasp.net/codeSection.aspx?csID=20 Thanks. Mark PM - System.Xml -- This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm. "cribeiro" <cribeiro@lexxa.com.br> wrote in message news:OclagBccDHA.652@tk2msftngp13.phx.gbl... > Does anybody have...

Possible to chart data for dates implicit within a range?
I have a list of computer programs with start and end dates for each. I need to determine how many programs were running on my system on any given day. Can I get this data for individual days between the start and end dates without creating columns for each intermediate day? Ex.: Program 1 ran from 01/01/2005 to 01/14/2005. Program 2 ran from 01/06/2005 to 01/22/2005. Is it possible to chart the total number of programs running on 01/08/2005 without manually creating a column for that date? Thanks, Nechama You can compute this with formulas. Star with data in A1:C4 as follows: ...

Automatic formatting of minimum/maximum value in a range.
I want that if I select a range of numbers the minimum or maximum should be selected by a single command How about cond. formatting? 1. Select the range. 2. Go to Format > Conditional Formatting 3. Select "Formula Is" and copy in this: =OR(INDIRECT("rc",0)=MAX(rng),INDIRECT("rc",0)=MIN(rng)) where "rng" is a defined name for your range. HTH Jason Atlanta, GA >-----Original Message----- >I want that if I select a range of numbers the minimum or maximum should be >selected by a single command >. > What do you want to do with t...

Multiple Data files--two with same name!
I have been having trouble balancing my checking account, and discovered that I have three data files: My Money, My Money0 and My Money0. Yes, two of them appear to have the exact same name. One of the My Money0 files appears to cover the time from my last archive (Jan 2003), while the other two files cover only a portion of that time (Jan 04-Aug 04) and (Jan 03-May 04). How can this happen? Any suggestions on how to fix it? At this point my only idea is to manually cross check all the entries and create a single data file then delete the other two. Any better ideas? In micro...