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

Similar Articles

[PageSpeed] 11

 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:

name on e-mail
hello, when i send an e-mail from our account it shows up as my husbands name instead of the actual e-mail address. How do we change this so our last name pops up instead of his? gfawga wrote: > hello, when i send an e-mail from our account it shows up as my husbands name > instead of the actual e-mail address. How do we change this so our last name > pops up instead of his? Go into the e-mail account that you defined inside of Outlook and put whatever string you want in the Your Name field. ...

Dynamics GP Web Service Install Error
I have installed the default databases for Great Plains 9.0 along with SP2 on a standalone Windows 2003 Server that is not a domain controller. When I go to install Dynamics GP Web Services I get an error just before it finishes that indicates the following: ADAM Error... The value entered for Administrator is not a valid user account. In the unattended setup file, type a valid user account in the Administrator field, and then run Setup again. Does anyone have any advice to get me past this point? I forgot to mention the error code: Error Code: 20020 "Dennis" wrote: >...

Calculate max value in specific range
Hi all, I've got a table with specific date values (start & end dates) like this: 3/23/00 Start 3/26/00 End etc. note: the length of days will vary from time tot time. -- I also have a table with all date values and corresponding numbers, like this: Column: A B Row: 1 3/23/00 1101.16 2 3/24/00 1106.16 3 3/25/00 1143.07 (=MAX) 4 3/26/00 1137.54 -- How can I calculate the max value from all those corresponding numbers between a Start- & End date? (1143.07 in this case) I've already tried the Vlookup and Max function but that only refers to 1 value (as...

Email trouble with New User in Active Directory
Below is a trail of posts that have been made. My problem has yet to resolved and I need some help quickly. The only other thing I can think of is to reinstall Exchange Server 2003. Hopefully someone will know the answer. Thanks! Subject: Re: Email not Setup when new user created in Active Directory From: "Bryan Hill" <bthill@comcast.net> Sent: 8/15/2004 12:30:21 PM We have other clients that are working fine. Just when we try to set up new ones - it will create the SMTP address but not the X400 address. I tried manually forcing the X400 but it will...

Trouble with the template.
I want to reset the 'normal' template to default to "Times New Roman" 10point font, and the pages numbered centre top. This seems to work when I set and reopen Word2003 from the Administrator Template vis: C:\Documents and Settings\Administrator\Application Data\Microsoft\Templates. But it will not work when I open Word2003 normally from the desktop. I have deleted the "Normal.dot" from the following and copied in the "Normal.dot" from the Administrator above: C:\Documents and Settings\All Users\Application Data\Microsoft\Templates...

trouble with CrmIsapi.dll
I was experiencing the same problem with the SDK as many have posted to this group ... "the WhoAmI() call results in a http error 405 when running sample code" I followed some of the advice; checked my .srf mapping to CrmIsapi.dll and re-registered the CrmIsapi.dll. when I re-register CrmIsapi.dll I get an error 80004005 - unspecified. ??? I copied it to various places and retried it ... same result. I got the original from the disk and retried ... same result. unmapped it from .srf, restarted and retried to register it ... same result. Anyone experienced this. What's up with m...

User folder on desktop has a bad name
My name is Matteo (like User Name). Hence I had a User Folder on desktop named Matteo. Now it has a different name, but files inside are C:\Users\Matteo The wrong name is visible only on desktop How can I restore the correct name? "Allamarein" <matteo.diplomacy@gmail.com> wrote in message news:c9cd2878-a7ad-46c5-b802-a37c9d4f5b7a@d39g2000yqa.googlegroups.com... > My name is Matteo (like User Name). Hence I had a User Folder on > desktop named Matteo. Now it has a different name, but files inside > are C:\Users\Matteo The wrong name is visible only on deskto...

Trouble with Outlook 2003 and PST File
Hi, We have a user who is using Outlook 2003 and moves most emails to a PST file which is considered archived email data. They have reached the Max limited on this PST file of 1.99GBs and its not letting them open the PST file. It is trying to repair it when they try to open the PST file. Here is the message they receive: "Can't move the items. The file <path>\<filename>.pst has reached its maximum size. To reduce the amount of data in this file, select some items that you no longer need, and then permanently delete them." I can't even get it open to delete...

unexpected conversion trouble
I wanted to open an Excel file I hadn't been working on for a while, an Excel couldn't do it. After some investigations, I found that even if the file still had th .xls extension, it seemed to be conveted in .doc format (in fact, whe I open it in notepad, I can see at the end some words that look like MSWord signature). I don't know how this happened, but I'm reall embarassed beacause i really need the data in this file. I tried to change the extension to .doc, open the file in Word and the import in Excel, but I can hardly see part of the file. What's more, i is a 3-she...

I am in trouble
Hi, I am a student in Cambodia.I don't have any girlfriend. I tried everything. I looked at them wrote poem to them it doesnt work. I got an account from facebook, hi5 friendster myspace. I have 0 girl friend. What should I do? I am so desperate. I am good looking and 250 kg only. Please help me or at least send me some foood I am hungry Best Regards Hungry Guy __________ Information from ESET NOD32 Antivirus, version of virus signature database 4944 (20100314) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com You're tr...

Trouble with Pivot Tables & Named Ranges
Can I use a dynamic named range setup in one file as a range in pivot tables in another file? When I have the data source file (with the named range) and the pivot report files open, the pivot reports update just fine. But the moment I close the source data, the pivot reports no longer can update and I get a message that the source can't be found. If I reopen the source, the pivot tables work fine again - but as soon as I close it up, the reports no longer work. The source file is very large and I don't want to open it every time I have to update all the reports tha...

Trouble with "time" in formulas
I have a time sheet that looks something like this: A1=time...A2=time...A3=A2-A1+if(A1/24,1)...A4=if(or(A3<time(0,15,0),A3>time(4,0,0)),A3,time(4,0,0)) A1 and A2 are set up as dropdown lists and the times in the list are i increments of 15 minutes. WHat I'm trying to accomplish is (in cell A4 if A3 is between 15 minutes and 4 hours, then return 4 hours. if A3 i 0 or greater than 4 hours, return A3. If I enter enter times in A1 around 00:00 and A2 a little after 00:0 it works fine... example: A1=22:00...A2=01:00...A3=03:00...A4=04:00 A1=20:30...A2=00:15...A3=03:45...A4=04:00 A1=00...

trouble
I have been using my MS Publisher 2000 for over 3 years. Just recently I attempted to access the Publisher program and got the following message: "This application must be installed to run. Please run setup fom the location where you originally installed the application." I have done everything I know to fix this including uninstalling and reinstalling the program. I don't understand what happened overnight nor do I understand the instruction "run from the location you originally installed the program". I can not access the numerous publicaions that I have cr...

Can I name a column and use it in a formula? (Excel 2007)
I've used named cells many times and they make the formulas much easier to read and much less error-prone. Can this be extended to a column of calculations? Suppose I want to calculate the area of a table of rectangles. My data might look like this: A B C 1 Height Width Area 2 2 3 6 3 3 5 15 4 8 6 48 The formula in C2 is "=A2*B2", which is not as easy to read as "=Height*Width". Is there a way to name Column A "Height" and Column B "Width" and then be able to put something li...

Trouble sorting a table???
Using Excel 2002) I am relatively new to Excel and can only really perform simple functions in a basic spreadsheet. The spreadsheet I'm having trouble with is pretty basic, but I'm having trouble sorting some of the results from a table. (I'll try and explain as best as I can) I have 10 people that I'm tracking 5 different things for (dates, debts, credits, items, number of transactions, and their balance as calculated in this table by simple SUM functions) I then have another table with these same 10 people with their total balance. This is the table I'm having t...

API TO get User Name
What is an Win32 call to get the user name in the form of COMPUTER\\User ? >What is an Win32 call to get the user name in the form of COMPUTER\\User ? Have a look at GetUserNameEx NameSamCompatible Dave Hi Michael, Yes, just as David provided, GetUserNameEx will help you to retrieve the user or other security principal associated with the calling thread. If you got a different token other than the current calling thread, you may first call ImpersonateLoggedOnUser by passing the token to impersonate the token in the current thread and then use GetUserNameEx to obtain the user ...

Having trouble
I have a set of numbers, a-e. A usually equals e but can be less. B remains constant. C is the sum of A and B. C cannot exceed D. D remains constant I have tried the following formula in the box for A, but C ends up being less than D when I want it to equal D =IF(C>D,E-(C-D)) What am I doing wrong? Uhh, I'm a little slow, but; If you are putting this formula in A and it's the same "A" that is part of the "C=Sum A & B" then I think you have a circular reference. In other words, you're asking xl to resolve "A" by asking "If C> D&quo...

Reuse Investment Name
How can I tell Money 2006 Prem to reuse an investment name that I deleted? When I try to do that, it gives me an error message that says I cannot do that, choose a new name. Tony In microsoft.public.money, TonyK wrote: >How can I tell Money 2006 Prem to reuse an investment name that I deleted? >When I try to do that, it gives me an error message that says I cannot do >that, choose a new name. Try entering a fake dividend for the deleted security. If that works, Money will un-delete the investment. Then delete the fake dividend. That did not work, but it gave me an idea. ...

Skipping a range in y-axis
I have a graph with a y axis that goes 0.0 to 1.6. I want to skip part of the that range in the y-axis, from around 0.7 to 1.2. I saw a tutorial on how to skip, say, 0.0 to .7, but not the middle range that I would want. Any ideas? Thank you!! Jason Hi have a look at http://www.tushar-mehta.com/excel/newsgroups/broken_y_axis/index.html http://peltiertech.com/Excel/Charts/axes.html#Broken for some tutorilas how to create a 'broken' y-axis -- Regards Frank Kabel Frankfurt, Germany Jason Silverman wrote: > I have a graph with a y axis that goes 0.0 to 1.6. > > I want ...

Restrict items to customers
I have a client that sells pharmaceutical and non pharmaceutical products. Is it possible to restrict certain items to certain customers to avoid that a product for a drugstore cannot be sold to a supermarket. Thanks, -- Toni Sort of but not 100%...here is how. Use customer part numbers. You can have only one part number for a specific item but assign it to the customer using the customer part number cross reference table. So say part 100p is a CVS product with CVS labeling. Enter the part in your inventory then list it in the customer part number cross reference under customer ...

E-mail shortcut name
I had this working once, but it stopped. In Outlook 2000, I have a contact for my e-mail address. It has my name, my e-mail address, my phone number, my fax number, and a nickname (me). If I write a new message and put in most of my name, I get a choice between sending it to my e-mail address, my e-mail address, or my fax-number. Choosing one of the two choices of e-mail address works. (I can't tell them apart). If I enter "me", I get a choice of two people in my global e-mail contacts at work whose names start with "Me". How do I get it so I can write an e-ma...

Print Preview troubles
When I do print preview, the information in the scratch area gets overlaid on the document, and there are text boxes where there are none in the work area. In the work area mode, the docs look correct and the stuff I have in the scratch area is OK. It is a scrambled mess. I have used "bring to front" and its' mates-no help. Using Publisher 2002 to create a 8 page booklet on 11x17 stock. I may add: this document had been working but something happened to it, most likely my fault, but I do not know what. Thanks, Jim This was a bug in 2002. Drag the objects to the top or bottom in ...

Outlook 2000 and Auto Filling the Send To Name
My CIO has Outlook 2002 and reported that when he fills out the "Send To" box, he gets the red line under the user name, instead of the name he last picked. We have several users setups with two e-mail addresses, Smith, Joe and Smith, Joe (Pager). Apaprently it used to defualt to which ever one he last used. Also, when he types SMI, it used to pull up the names beginning with SMI , like Smith, and it also no longer does that. I have not been able to locate any area that may control this. Any ideas? Thank you Mark Winning OK, I just found the settings location at tools - options ...

Problem with Microsoft Dynamics Business Portal in sharepoint Web
I have installed sharepoint by my username which is not sytem administrator user name.then we installed Microsoft dynamics business portal GP by my username .The installation did not work. then we reinstalled Business Portal by system administrator user name also it did not work.Does any one Knows how to fix this problem without formatting the server and without reistall sharepoint by system administrator user name? ...

Conditional formatting-Date range
I am creating a spreadsheet which is a checklist for a shipping processs. At the top I key in customer name and date the shipping process starts- let say Feb/15/2010. I require a number of documents that prompt other portions of the process. Therefore I need an indicator if I do not receive these within 7 days of the start date ie: Column 1 Column 2 Date received Date verified If column 1 remains blank (no date entered) and is past the start date by 7 days I would like it to turn red. NEXT if there is a date entered in that cell I need an additional c...