Dynamic Charting Question

I'm attempting to set up a dynamic chart.   So far, I've defined a name for 
cells B33:M33 (using the following offset equation).  
=OFFSET(Sheet1!$B$33,0,0,1,COUNTA(Sheet1!$33:$33)-1)

I'd like to be able to add more columns after M and the chart will 
automatically pick it up.

We have a graph that currently includes B33:M33 as the X axis values for a 
line chart.   How do I change the information in the chart to include the 
determined dynamic range for X?  

Thanks in advance,
Barb Reinhardt


0
1/11/2006 2:10:04 PM
excel.charting 18370 articles. 0 followers. Follow

4 Replies
431 Views

Similar Articles

[PageSpeed] 56

I've figured out what I need to edit to get the series to change, but is 
there an easier way that editing each series?

"Barb Reinhardt" wrote:

> I'm attempting to set up a dynamic chart.   So far, I've defined a name for 
> cells B33:M33 (using the following offset equation).  
> =OFFSET(Sheet1!$B$33,0,0,1,COUNTA(Sheet1!$33:$33)-1)
> 
> I'd like to be able to add more columns after M and the chart will 
> automatically pick it up.
> 
> We have a graph that currently includes B33:M33 as the X axis values for a 
> line chart.   How do I change the information in the chart to include the 
> determined dynamic range for X?  
> 
> Thanks in advance,
> Barb Reinhardt
> 
> 
0
1/11/2006 2:21:05 PM
I am not sure I understand the problem.  If you have a name, say XVals, 
defined to be the OFFSET(...) formula and your series formula reads 
=SERIES(,{bookname}!XVals,{bookname}!YVals,...) then the series should 
automatically expand as you add data in M, N,...

So, what am I missing?

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <81889D24-19D6-4425-ACDB-2F47FBCCCF73@microsoft.com>, 
BarbReinhardt@discussions.microsoft.com says...
> I'm attempting to set up a dynamic chart.   So far, I've defined a name for 
> cells B33:M33 (using the following offset equation).  
> =OFFSET(Sheet1!$B$33,0,0,1,COUNTA(Sheet1!$33:$33)-1)
> 
> I'd like to be able to add more columns after M and the chart will 
> automatically pick it up.
> 
> We have a graph that currently includes B33:M33 as the X axis values for a 
> line chart.   How do I change the information in the chart to include the 
> determined dynamic range for X?  
> 
> Thanks in advance,
> Barb Reinhardt
> 
> 
> 
0
1/11/2006 2:51:20 PM
The series reads:

=SERIES(Sheet1!$A$42,Sheet1l!$B$33:$M$33,Sheet1!$B$42:$M$42,1)

I have XVals defined using the offset( ... ) formula currently as $B$33:M$33 
and YVals as $B$42:$M$42 using offset () 

Do I have to manually go into every series and change the values so that 
they use XVals and YVals?

Thanks,
Barb Reinhardt
"Tushar Mehta" wrote:

> I am not sure I understand the problem.  If you have a name, say XVals, 
> defined to be the OFFSET(...) formula and your series formula reads 
> =SERIES(,{bookname}!XVals,{bookname}!YVals,...) then the series should 
> automatically expand as you add data in M, N,...
> 
> So, what am I missing?
> 
> -- 
> Regards,
> 
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
> 
> In article <81889D24-19D6-4425-ACDB-2F47FBCCCF73@microsoft.com>, 
> BarbReinhardt@discussions.microsoft.com says...
> > I'm attempting to set up a dynamic chart.   So far, I've defined a name for 
> > cells B33:M33 (using the following offset equation).  
> > =OFFSET(Sheet1!$B$33,0,0,1,COUNTA(Sheet1!$33:$33)-1)
> > 
> > I'd like to be able to add more columns after M and the chart will 
> > automatically pick it up.
> > 
> > We have a graph that currently includes B33:M33 as the X axis values for a 
> > line chart.   How do I change the information in the chart to include the 
> > determined dynamic range for X?  
> > 
> > Thanks in advance,
> > Barb Reinhardt
> > 
> > 
> > 
> 
0
1/11/2006 3:02:04 PM
On a one-time basis, yes.  Otherwise, XL has no way of making the 
connections between the names and the series data.

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <F308F5C4-28DF-4D01-A1F8-FD2DFC8B7FF2@microsoft.com>, 
BarbReinhardt@discussions.microsoft.com says...
> The series reads:
> 
> =SERIES(Sheet1!$A$42,Sheet1l!$B$33:$M$33,Sheet1!$B$42:$M$42,1)
> 
> I have XVals defined using the offset( ... ) formula currently as $B$33:M$33 
> and YVals as $B$42:$M$42 using offset () 
> 
> Do I have to manually go into every series and change the values so that 
> they use XVals and YVals?
> 
> Thanks,
> Barb Reinhardt
> "Tushar Mehta" wrote:
> 
> > I am not sure I understand the problem.  If you have a name, say XVals, 
> > defined to be the OFFSET(...) formula and your series formula reads 
> > =SERIES(,{bookname}!XVals,{bookname}!YVals,...) then the series should 
> > automatically expand as you add data in M, N,...
> > 
> > So, what am I missing?
> > 
> > -- 
> > Regards,
> > 
> > Tushar Mehta
> > www.tushar-mehta.com
> > Excel, PowerPoint, and VBA add-ins, tutorials
> > Custom MS Office productivity solutions
> > 
> > In article <81889D24-19D6-4425-ACDB-2F47FBCCCF73@microsoft.com>, 
> > BarbReinhardt@discussions.microsoft.com says...
> > > I'm attempting to set up a dynamic chart.   So far, I've defined a name for 
> > > cells B33:M33 (using the following offset equation).  
> > > =OFFSET(Sheet1!$B$33,0,0,1,COUNTA(Sheet1!$33:$33)-1)
> > > 
> > > I'd like to be able to add more columns after M and the chart will 
> > > automatically pick it up.
> > > 
> > > We have a graph that currently includes B33:M33 as the X axis values for a 
> > > line chart.   How do I change the information in the chart to include the 
> > > determined dynamic range for X?  
> > > 
> > > Thanks in advance,
> > > Barb Reinhardt
> > > 
> > > 
> > > 
> > 
> 
0
1/11/2006 4:53:49 PM
Reply:

Similar Artilces:

Moving Dynamics
Can someone direct me to updated infomation on How to move GP 10 to a new server box with SQL 2008 KB article 878449 still applies and has updated information for SQL Server 2008. -- Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "tstrop" wrote: > Can someone direct me to updated infomation on How to move GP 10 to a new > server box with SQL 2008 ...

Build Dynamic Query from Form
I am trying to build a dynamic Query from a Form. I keep getting an error that reads ‘Object qryFilter already exists’ I suspect it has something to do with the string of dates being passed to the Query; strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom] And [Forms]![SearchForm]![cboTo])" I am trying to add a means for a user to Query by Customer and Trader AND all records between two dates. This was working fine for Customer and Trader; when I added in the code to filter by dates I started having problems. I know the SQL will be li...

Has Anyone ever seen a chart like this?
Looking for some help. I need to know what this column chart is called? Or how to make it? 'CLick Here to View Chart' (http://www.f150online.com/galleries/pictureview.cfm?pnum=163585&anum=11244) I can't find any option in Excel to make this chart. Maybe it was not made in Excel? Or I need a specific plugin? -- TsunamiBob ------------------------------------------------------------------------ TsunamiBob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36202 View this thread: http://www.excelforum.com/showthread.php?threadid=559874 Hi, It...

show another column when hovering on a point in a chart
Is it possible to show another column when you hover on a point? I have 3 columns, A, B, and C for a scatter plot I have B and C as x and y coordinates of a point, and I'd like to have Excel show A when I hover on the point. thanks, Wei ...

Question On Fields
What is the (Microsoft desciption)difference between the SIC field and the Industry Code field? To me they seem like a duplication. Thanks! Shauna Hi, According to the Deployment Manager descriptions of attributes: SIC - Standard Industrial Classification code for the Account industrycode - the type of industry with which the account is associated... Hope this helps! "Shauna Koppang" <anonymous@discussions.microsoft.com> wrote in message news:034101c48af4$62524a70$a401280a@phx.gbl... > What is the (Microsoft desciption)difference between the > SIC field and the...

charting newdata
Formula or VBA? I am trying to create a formula to assign a region to populate a chart. I know there is a way to use the OFFSET function to point ot a certain section of a data sheet, but what if i insert a line between the title and the data.(done programmactically)? the problem is that inserted lines do not automatically include into the data range for a chart. The data wants to be sorted from newest to oldest. Would it be easier to create a formula to assign a 'chart data area' or simply reassign the data area every time the table is updated Thanks for the input. ...

chart label reference based on the column number
In a worksheet with an embedded chart, I have a cell, S4, where I enter the number of the column I want to chart (these numbers are listed as labels in cells A2:R2). I can't figure out how to translate the number in S4 into the corresponding column letter. For example, if S4 contains "3", the chart title should be =$C$2. I think working with absolute references, R1C1, might be easier here, but somehow what I've tried, didn't work (the entire spreadsheet is based on relative addresses (A1). z.entropic in a cell (Z100) put =index(A2:G2,1,S4,1) Change G2 to whatev...

Formula Question #18
I have built a workbook in which I have inserted a formula to tell me whether the contents of a supply bin needs replenishment or not. The formula I used is: =IF(E3>F3,"REPLENISH!","No Action"). Each morning, I run a report to see what parts have been used, which becomes a new sheet in the workbook. Now, I want to add a formula that, whenever it sees "REPLENISH!," it will back through the workbook to count whether that same part needed replenishment on consecutive previous days. If it has, then the latest worksheet will report the number of days that ...

Exchange 2003 Question #3
Dear all, I have upgraded from Exchange 5.5 and notice that I miss the feature where I can clean/delete mail based on certain criteria from users mailbox via the System Manager. Is this feature still available in Exchange 2003, If so, where is it? Thanks. Regards, Kueh. You can use ExMerge to remove emails based on certain criteria (ie. attachment name, subject, etc). http://www.msexchange.org/tutorials/MF013.html "KA Kueh" wrote: > Dear all, > > I have upgraded from Exchange 5.5 and notice that I miss the feature where I > can clean/delete mail based on ...

newbie question
Excel help says it should be a tab under tools, but I can't see it in my version. I would appreciate any help. Thanks. You must first load the Solver Add-in through Tools>Add-ins. Gord Dibben MS Excel MVP On Sat, 03 Nov 2007 07:46:34 -0700, analyst41@hotmail.com wrote: >Excel help says it should be a tab under tools, but I can't see it in >my version. > >I would appreciate any help. > >Thanks. On Nov 3, 10:56 am, Gord Dibben <gorddibbATshawDOTca> wrote: > You must first load the Solver Add-in through Tools>Add-ins. > > Gord Dibben MS E...

question about using the correct schema namespace
I have a Access/VBA client that exports xml to the local drive, then posts it over http to an aspx page. The aspx page consumes it, then builds itself based on the xml data. This works find only if I first mannually change the root entry's namespace url as shown below. Does anyone know how I can get the two (cleint xml export and aspx xml consume) to work together with the appropriate namespace? Here is the top three lines of my xml export (prior to mannually changing it): <?xml version="1.0" encoding="UTF-8"?> <root xmlns:xsd="http://www.w3.org/200...

AD/Network design question
Hello all I have a 2 sites which users come and go from. These sites have 2 different network ip domains (192.168.1.x and 192.168.2.x). Users need to be authenticated using the same user id and password at both sites. DC/GC is at 192.168.1.x and is a SBS 2003. Server at 192.168.2.x is Server 2003. When the server at 192.168.2.x is connected via vpn to 192.168.1.x all is well. How do I get the server at 192.168.2.x to act as a AD/DC when it’s not connected to 192.168.1.x ? Thank you Hal I think you will run here into the limitations of SBS :-( @ SBS experts : ...

REQ: Can Someone Help Me With This Outlook XP Question?
Hello All: I use Word to edit my e-mail msgs in Outlook XP. I had to reinstall Office the other day and now whenever I want to start a new e-mail or reply to an e-mail I get a warning that comes up: "A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected it may be a virus and you should choose 'No'" There is a box that asks for the amount of time to allow the access: 1 to 10 minutes. Do I have to have specific settings for my Outlook address book? I use the Contact area in Outlook for addresses. I have ...

DEADLINE... PLEASE HELP! Stacked Bar chart?
I'm not even sure how to ask the question so here's what I have... 2003 2004 2005 Actual/Goal Actual/Goal Actual/Goal Me 1009/1061 591/866 658/897 Comp. A 966/1012 633/811 624/808 Comp. B 699/744 450/593 480/607 Comp. C 957/1005 642/821 665/838 I wanto to show a bar for each competitor, for each year, so there will be 4 bars for each year. Each bar showing Actual performance & Performance Goal...

Core Chart
Need a picture of an opened sphere showing core and layers. Can you help? After managing to set up OE-QuoteFix on his new PC, Ed reads a message from Isabel <ialduen@catholiccharitiesden.com>... > Need a picture of an opened sphere showing core and layers. a) The core of what? A black hole? An apple? A nuclear power plant? b) This is a Microsoft Publisher group. Your question is hardly relavent to the topic of discussion really. Why did you choose this group? -- Ed Bennett - MVP Microsoft Publisher http://www.mvps.org/the_nerd/ Before reading this message, view the disclaimer...

Mail merge & staple question
Does anyone know how to use mail merge in Publisher with a printer that folds and staples?? Publisher is sending it to the printer as "one" big file and trying to staples "all" my newsletters together instead of individual ones with the addresses on them. Or if anyone has any other programs or ideas on how I can accomplish this task, sure would be appreciated. Thanks While waiting for decisions from his 6 university choices, Ed sees a message from Parishsecretary <Parishsecretary@discussions.microsoft.com>. On it is written: > Does anyone know how to use mai...

z-order of various lines and bars in an Excel 2007 chart
What is the procedure for changing the z-order of various lines and bars in an Excel 2007 chart, such as drop lines, high-low lines, up/ down bars, and error bars? I observed that when I add drop lines to an Excel 2007 chart, they appear in front of the underlying data -- in my case, the underlying data is displayed as a plotted area. I want the plotted area to be fully visible with no lines over it. I would like to send the drop lines to the back and keep the plotted area in the front, but there is no option to do this. So, is there a procedure for adjusting the z-order of drop...

Hierarchy Pyramid Chart
Is there a way (Excel 02), to create a chart that is in the shape of a pyramid/triange (i.e. like a pie graph, but in a triangle instead)??? It doesn't seem to be an option, but maybe some type of addin??? Thanks in advance Nevermind - I realized that the pyramid is actually a diagram. So I inserted a Diagram - Organizational Chart instead and it's just what I needed!!! "Keep It Simple Stupid" wrote: > Is there a way (Excel 02), to create a chart that is in the shape of a > pyramid/triange (i.e. like a pie graph, but in a triangle instead)??? > It doesn't ...

Import Templates for Dynamics CRM 3.0
Hi all I am trying to use the standard import file into Leads, and it is coming up with data type not valid, length not valid however I am 100% sure it is correct. Have any of you out there got formatted xls files that have the correct data types. field lengths that we could possibly use as a template to see if it is actually my error? Regards Wayne Lockey ...

Office 2010 Buying Question Assistance Needed
I've been looking through the MS Office 2010 web site to try to determine what my new small company would require, but I can't find the information I need. We for sure would need Office Pro Plus, but other than that I'm not sure. We want to run it on our own server. We will initially have 3-5 people using it and perhaps more later on. Would we need to purchase site licensing? Unfortunately, our programmers are MS haters (I'm not) and I can't get any assistance from them on this, but I have power of the pen. I would appreciate any assistance I can get. Th...

Baseball Stats question: How can I get the RBI's?
I have a data table that looks like this: Code ------------------- STR-S SNK-S STR-K STR-F SNK-S STR-3 STR-2 STR-F SNK-S SNK-4 SNK-S STR-3 SNK-F STR-4 STR-O ------------------- "STR", etc at the beginning are pitch types, SNK is Sinker for example The end character is the result of the pitch, a 4 would be a home run, 3 a triple, and so forth. O is out, F is foul, S is strike --- you ge it. How can I figure the amount of RBI's? I know that it's 5, but I can' think of any automated way to calculate this, anyone got any ideas? THANK YOU!! :confused -- AVER...

Question About Missing Data
So, this is probably really easy, but I just want to ask and see if I may be missing something here. Some data on vendor numbers changed. Let’s say IBM used to have a vendor number 12345 and now it’s vendor number is 56789. I can identify IBM as IBM, but I really want to use the number, not the name. Should I set up a table that ties the numbers together, so that Access knows 12345 = 56789? Or, should I do some kind of Update Query and change all incidences of 12345 to be 56789? Or, is there some other, method, like a ‘best practices for missing data’? Thanks! Ryan--- ...

Custom CRect Question
I have a custom rectangle class that inherits from CRect: class CCustomRect : public CRect { private: CPoint m_maxPt; COLORREF m_color; TCHAR text[50]; public: CCustomRect(RECT* source, CPoint pt, COLORREF rgb); RECT* RectBase(); void Update(RECT* r); } Inheritance has worked well until I found myself needing to create the RectBase function (above) to return the rectangle dimensions. CRect does not seem to have any methods that can be called to return the base class's RECT value. I could take CRect::Size and construct a rectangle to return, but this seems a bit much. My...

Excel link update question
I need to maintain an excel workbook which contains a lot of links to other workbooks. Since the linked workbooks change every week, I need to change all the links accordingly. For example, a cell with formula "='[aug_28.xls]sheet1' !A10" will be changed into "='[sep_4.xls]sheet1'!A10". I tried to do this with Find/Replace. However, the link is updated every time it is changed. The link updating takes a couple of seconds. So it may take a hour to finsh it for a workbook with thousands of links. Could anyone tell me how to shut down the link updating when...

How do I lock a chart so it will not update?
That's the question. I have my data in Excel and the chart in Excel but not all the data cells are used. Everytimg I open the chart it wants to update and I want it to stay the same. Any ideas on how to lock the chart? Hi Just a few ideas: You could lock the cells that are shown in the chart. Or you could copy the cells and paste as values (assuming formulas were used that update when other cells change). -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "Locking a Chart" wrote: > That's the question. I have my data in Excel and the chart in ...