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

Similar Articles

[PageSpeed] 13

 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:

How can you define Page name as a function of other variables.
I'm using visio 2002. I am able to include the value of the "Page name" to define anothe variable, simply by referencing "pagename()" in the formula. For example:thetext=pagename()&"hello" However, is it possible to define the "Page name" as a function o other values on the sheet? For example: Could I define the page name in this fashion : Pagename()= "hello"&shapetext(bluebox.39!thetext) where "bluebox.39" is a shape on the sheet. If so where do I do this..? I can't find the pagename cell on the pag shapeshee...

MS Dynamics GP 10.0 SP3
Dear All Previously i've managed to successfully install Workflow on a client's live server. This is after 1-week of many support calls and emails. Now funnily enough; i'm replicating the same installation procedures on 2 virtual server images. Here's the basics Server 1 (AD/Domain Controller) ----------------------------------------- Windows Server 2003 Std Ed SQL Server 2008 GP 10.0 Server 2 (Webserver) ----------------------------------------- Windows Server 2003 Std Ed MOSS 2007 SP1 GP 10.0 - Web Services GP 10.0 - MS Sharepoint Wizard As it is; i've checked and ...

highlight high and low ranges in chart
Trying to chart the acutal readings of blood sugar on a chart on which the normal range say between 90 and 120 are highlighted on the chart. If I try to insert a text box and if a reading falls in the normal range, the reading is hidden. I tried to group the plotted readings and the text box to send the text box behind the readings but could not. Hi, One of many possible ways is to use a helper column. Suppose your dates/times are in A2:A20, and your readings iare in B2:B20. In C2, enter =IF(AND(B2>=90,B2<=120),NA(),B2) and copy down to C20. This will give a seriescontaining onl...

Dynamic Chart Title With 3D Stacked Column Chart
Excel 2007 I have a 3D stacked column chart that contains two data series. My data looks like this: Month Warnings Critical Incidents Misc Incidents January 12 5 1 February 8 7 3 March 7 12 1 I would like to add a chart title that contains the current year. I would like the year number in the title to update automatically. With a 3D clustered column chart, I can do that by putting a formula in a cell, and including that cell in the chart range. I can&...

Automatically creating file names
Does anyone know how to automatically create filenames from data in an existing spreadsheet? ...

Outlook user name error?
Outlook 2003 Comcast POP3 I get an user name error when trying to setup outlook to be the client for comcast mail. The comcast servers and ports are setup properly. I was wondering if the username convention might be a problem for my mother first.last@comcast.net I setup my account on her machine as xxxxxxxx@comcast.net and it went just fine. Is there a problem with the . (dot) in the user name field that outlook does not like? On web mail the user name/password works fine. Any help appreciated. She really likes outlook. -- ***************** John Lenz JohnLenz@comcast.net John...

Add a name to selected cells
In a sheet I have a variable selection of cells. I want to add a name (overviewselection) to these cells with a macro. How should I do that? More detail "Hook em horns" -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "jgmiddel12344" <jgmiddel12344@discussions.microsoft.com> wrote in message news:72967A58-9F3D-4B71-AFA1-9A6FC55FF0FC@microsoft.com... > In a sheet I have a variable selection of cells. I want to add a name > (overviewselection) to these cells with a macro. How should I do that? The selected cells are on a sheet...

How do you set your name/address etc at end of e-mail?
How do you set your name/address etc at end of e-mail so that it will always be sent with every e-mail? Putting a footer in will always put this info too far down the page. Tools>Options>Mail Format (tab) and Signatures button near bottom. "octo" <octo@discussions.microsoft.com> wrote in message news:BD50577A-C970-47D2-83D7-4CC7634F448F@microsoft.com... > How do you set your name/address etc at end of e-mail so that it will > always > be sent with every e-mail? Putting a footer in will always put this info > too > far down the page. ...

Range Equation
Please help, Thanks Sub Range_equation() Dim catalyst As Variant Cells(1, 3) = "=""ABC""" ' as given Cells(2, 3) = "568" ' as given catalyst = Range("C1:C2") Range(Cells(1, 1), Cells(2, 1)) = catalyst catalyst = "" ' My question is why do I need a catalyst to perform the following equation? ' Range(Cells(1, 1), Cells(2, 1)) = Range("C1:C2") End Sub Hi! I dunno: but if you use Range(Cells(1, 1), Cells(2, 1))...

Extending a Chart range automatically
OK, can you people tell a thick twit how to automatically extend a chart range by rows as I type in more data each day? Thanks Hi have a look at http://peltiertech.com/Excel/Charts/Dynamics.html http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html -- Regards Frank Kabel Frankfurt, Germany Shane Nation wrote: > OK, can you people tell a thick twit how to automatically extend a > chart range by rows as I type in more data each day? > > Thanks ...

VBA Dynamic range in Pivot table
I need to code the following: I have a pivot table that may contain any number of companies in the row section and one item in the data section. I am trying to figure out how I can code something that will drill down each companies row total without naming the companies in VBA and then move the new worksheet to a new book and save it using data contained in one cell. Any suggestions? In the attached sample, I would want to drill down companies 1 - 10 and save each file using the name contained in cell B2 of the new workbook. Thanks in advance for any help or suggestions! +...

How to mention a particular sheet name while opening excel with VB script.
Hi Pls say me 1. How to mention a particular sheet name while opening excel with VB script. 2.how to know the rowcount and column count in excel using vb script. hi this is from a macro i use to use. reference the file name and complete file path. Workbooks.Open Filename:="S:\PUBLIC\AE-MRP40\DollarsByLoc40.xls" row count.... dim lastrow as long lastrow = cells(Rows.Count, "A").End(xlUp).Row 'where A is the column msgbox lastrow column count.... dim lastcol as long lastcol = Cells(1, Columns.Count).End(xlToLeft).Column 'where 1 is the row msgbox lastcol regards ...

Sum if Worksheet name contains certain data
I know =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255) will get me the name of a worksheet. Is it possible to base a SUMIF formla on the worksheet name? I'd like to set up something that adds cell B23 for all sheets that contain the numbers 150 in the worksheet name. I've tried the Conditional Sum wizard, but it doesn't recognize the set of worksheets as a valid range StephanieH Wrote: > I know =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255 > will get > me the name of a workshe...

Pivot Table
I created a pivot table graph. I have a ranking field that appear concatenated like {color} 1 - Confused {color} 2 - Ugh I need to change to {color} Confused {color} Ugh Using this code below, I can format the string but the Name is not updating Dim m as Integer Dim strmk As String Dim iLen As Integer For m = 1 To ActiveChart.SeriesCollection.Count iLen = Len(ActiveChart.SeriesCollection(m).Name) strmk = (Mid(ActiveChart.SeriesCollection(m).Name, 4, iLen)) ActiveChart.SeriesCollection(m).Name = "bob" Next m Excel 2007 PivotTable, PivotChart ...

Name automatically changes on MSN Messenger 7.0
Hi everyone. My brother is having a problem with MSN Messenger version 7.0 on Windows 2000. Sometimes when he signs in, his display name is "Dude", having been changed on its own. He ran a virus scan, it found something. I googled something to specifically find viruses that target MSN Messenger, that found nothing. This is a recent thing, within the last week or two. What is going on with this? Thanks. Nemephosis, I do not know the direct answer to your question, but two suggestions: 1. if Windows Security Essentials also runs on Win 2000, then a. download sa...

Send message with recipient's name showing
How to Send a message with the recipient's name showing in the To/CC/BCC field? Isn't that the normal state of things? I'm not really sure what you're asking here... "izumi" <izumi@discussions.microsoft.com> wrote in message news:670189A6-54A6-444D-9F3A-D5C3E1FB93EC@microsoft.com... > How to Send a message with the recipient's name showing in the To/CC/BCC > field? I would like to know how to send message with recipient's name showing, but without the email address shown when the recipent received it. "Vince Averello [MVP-Outlook]&q...

How to convert phone area-code to state name and time zone
I have a column with US phone numbers (all numerical, 10 digits). I would like to have the State name in a column right next to the phone number, and the Time Zone in the next column to the right. I have the master Area Code, State, Time Zone data in any format needed: one long string, range, VBA constant... The obvious solution is to write a VBA that uses the 3 left-most digits and scans the master area-code table to find a match. The only problem is that my list of phone numbers has several 10,000 numbers and scanning the master area code again and again will take hours... Any advan...

Dynamic Goto
I have a function in which I wish to use an input variable in conjunction with Goto, but it generates an error. Can this done? Function DoMyWorkForMe(sGoto as string) Goto sGoto Test: Msgbox "" End function When I run DoMyWorkForMe("Test") and give me a label not defined ("Étiquette non définie" - French PC). Is there a way to make this work or do I need to change my basic concept for my function? Thank you QB "QB" <QB@discussions.microsoft.com> wrote in message news:12B4CAF0-ACAF-4BA3-9111-18FAF87EC6...

Simple Data Backup for MS Dynamics CRM 3.0
Hi ALL, I want to have a simple data backup for MS dynamics CRM 3.0. Can anyone comment on the below backup steps: 1. Set the MS Windows 2003 Server daily backup to backup the folder "C:\Program Files\Microsoft SQL Server\MSSQL.1", "C:\Program Files\Microsoft SQL Server\MSSQL.2" and "C:\Program Files\Microsoft SQL Server\MSSQL.3". Copy all the data to extend USB HD; 2. Manual record down the AD structure, server version and CRM server version information; If the server got any problem, I will install another MS Windows 2003 Server, after that, config MS d...

How to change a global named reference to a sheet named reference?
How do I change a named reference that is visible (in the name box) on all sheets to just a single sheet? What makes a named reference visible on all sheets in a workbook as opposed to a single sheet? when you create the name you need to use the sheet name do Insert=>Name=>Define, select the current name and delete it. Assume the name was profit and you want it local to sheet3 name: Sheet3!Profit Refersto: =Sheet3!$B$2:$B$30 Now Profit will only appear in the name box when Sheet3 is the active sheet. -- Regards, Tom Ogilvy "Rob Hetlam" <.> wrote in message ne...

Expand a Named Range in 2007
Hi all, I'm actually an experienced user, but am now using Excel 2007 and can't find how to expand my named range. It's been changed and I'm not able to find it in the help section. HELP! I believe you want to change which cells that a particular name refers to right? If so you can use the Name Manager under the defined names group of the formulas tab. From there you can select the name then modify which cells does it refers to on the refers to textbox. -- Chester C. Coronel Junior BSIT Student University of Asia and the Pacific, Philippines Read the StudentEmpowered B...

Selecting email names HELP
Hi all, I am having a problem selecting email names from my address book. When I create a new email and hit the "To:" button a "Select Names" window pops up. I can see my contacts names and email addresses in the left hand box but when I highlight one to move it over to the "Message Recipients" box on the right the only thing that comes over is a ";" with no email address. If I open up the contact the email address is indeed there. Any help would be appreciated. Dean ...

"Name Conflict" when Excel 2007 open an old worksheet
Hi all, An "Name Conflict" error message pop-up "Name cannot be the same as a built-in name". That worksheet was created by Excel 2000 and also could open on Excel 2003 without any error message. Change the worksheet file name could not solve the problem after tested. Why? Thank you, Chik "Chik" <c...@netvigator.com> wrote... >An "Name Conflict" error message pop-up "Name cannot be the same as >a built-in name". That worksheet was created by Excel 2000 and also >could open on Excel 2003 without any error message. Change th...

Dynamics 9 Regional installation Error
Hello, I am installing GP9 from the MSDN website, but whenever i select UK & Ireland it installs the English US version instead, So Customer/Supplier are now called the american version, and also the Phone & Address fields are different, I know the latter can be changed by edoting the message ID boxes but the first concern is the most worrying Mark, Language packs are activated via registration keys. If you go to Help > About Microsoft Dynamics and choose Options, you can check the Language scrolling window to verify languages currently registered. Best regards, -- MG.- Mar...

Obfuscating machine name
I have a Win2K machine with Exch 5.5 installed that sends and receives all email thru another Win2K server running SMTP and a SPAM filter. On the SMTP machine I was able to "hide" the real machine name by changing the "primary DNS suffix" of the machine. I should add that our internal domain name is not the same as our external name. If I change the primary DNS suffix on my Exchange server so that the real name is not contained in the mail headers, how much stuff is going to break? Will that change the name in the mail headers? Thanks, Larry ...