Impossible - Dynamic Legend?

Hi, 

I have a table of 23 rows (products) and 30 columns (date) which plots sales 
of different items against the date against which they were sold. Not all 
products are sold every month. Using macros I can create the table that will 
summarise the sales per day in a particular month for any items that were 
sold, deleting all empty days and product lines. So my table can be anywhere 
from a 1x23 to a 23x30. I would like to create a dynamic chart that will 
plot the date on x-axis against the sales per day on the y-axis WITH THE 
LEGEND SHOWING WHICH PRODUCTS WERE MANUFACTURED. As the chart depends on the 
table my graph can have 1 to 23 lines on it, with 1 to 23 items in the 
legend. My table starts in cell AP5. 

How can I create such a dynamic chart and legend? 

Thanks
0
LiAD (16)
9/9/2009 4:16:01 PM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
414 Views

Similar Articles

[PageSpeed] 36

Start with a fully populated table. Create a chart from this table.

Use your macros to hide rows or columns that you don't want to plot. 
Excel charts by default do not plot hidden cells, so hiding what you 
don't want to show should do the trick.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



LiAD wrote:
> Hi, 
> 
> I have a table of 23 rows (products) and 30 columns (date) which plots sales 
> of different items against the date against which they were sold. Not all 
> products are sold every month. Using macros I can create the table that will 
> summarise the sales per day in a particular month for any items that were 
> sold, deleting all empty days and product lines. So my table can be anywhere 
> from a 1x23 to a 23x30. I would like to create a dynamic chart that will 
> plot the date on x-axis against the sales per day on the y-axis WITH THE 
> LEGEND SHOWING WHICH PRODUCTS WERE MANUFACTURED. As the chart depends on the 
> table my graph can have 1 to 23 lines on it, with 1 to 23 items in the 
> legend. My table starts in cell AP5. 
> 
> How can I create such a dynamic chart and legend? 
> 
> Thanks
0
jonNO1 (306)
9/10/2009 1:19:49 PM
Nice trick!!!

Simple but effetive.

As a lead on from this - I have 12 tables all in a line and would like my 
charts to be beside the table.  If I hide full columns I'd need the charts to 
be spread over a wide area to avoid hiding them so the user could never find 
them.

Is there a way of hiding part columns?  As in hide just A5-A13 etc?

Thanks a lot for your help

"Jon Peltier" wrote:

> Start with a fully populated table. Create a chart from this table.
> 
> Use your macros to hide rows or columns that you don't want to plot. 
> Excel charts by default do not plot hidden cells, so hiding what you 
> don't want to show should do the trick.
> 
> - Jon
> -------
> Jon Peltier
> Peltier Technical Services, Inc.
> http://peltiertech.com/
> 
> 
> 
> LiAD wrote:
> > Hi, 
> > 
> > I have a table of 23 rows (products) and 30 columns (date) which plots sales 
> > of different items against the date against which they were sold. Not all 
> > products are sold every month. Using macros I can create the table that will 
> > summarise the sales per day in a particular month for any items that were 
> > sold, deleting all empty days and product lines. So my table can be anywhere 
> > from a 1x23 to a 23x30. I would like to create a dynamic chart that will 
> > plot the date on x-axis against the sales per day on the y-axis WITH THE 
> > LEGEND SHOWING WHICH PRODUCTS WERE MANUFACTURED. As the chart depends on the 
> > table my graph can have 1 to 23 lines on it, with 1 to 23 items in the 
> > legend. My table starts in cell AP5. 
> > 
> > How can I create such a dynamic chart and legend? 
> > 
> > Thanks
> 
0
LiAD (16)
9/11/2009 3:33:01 PM
You can't hide partial rows or columns. What you can do is prevent the 
charts from changing shape.

Classic Excel:
Format Chart Area, Properties tab, Move but Don't Size With Cells

Excel 2007:
Chart Tools > Format ribbon tab, click little thingie at the bottom 
right of the Size group, Properties tab, Move but Don't Size With Cells

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



LiAD wrote:
> Nice trick!!!
> 
> Simple but effective.
> 
> As a lead on from this - I have 12 tables all in a line and would like my 
> charts to be beside the table.  If I hide full columns I'd need the charts to 
> be spread over a wide area to avoid hiding them so the user could never find 
> them.
> 
> Is there a way of hiding part columns?  As in hide just A5-A13 etc?
> 
> Thanks a lot for your help
> 
> "Jon Peltier" wrote:
> 
>> Start with a fully populated table. Create a chart from this table.
>>
>> Use your macros to hide rows or columns that you don't want to plot. 
>> Excel charts by default do not plot hidden cells, so hiding what you 
>> don't want to show should do the trick.
>>
>> - Jon
>> -------
>> Jon Peltier
>> Peltier Technical Services, Inc.
>> http://peltiertech.com/
>>
>>
>>
>> LiAD wrote:
>>> Hi, 
>>>
>>> I have a table of 23 rows (products) and 30 columns (date) which plots sales 
>>> of different items against the date against which they were sold. Not all 
>>> products are sold every month. Using macros I can create the table that will 
>>> summarise the sales per day in a particular month for any items that were 
>>> sold, deleting all empty days and product lines. So my table can be anywhere 
>>> from a 1x23 to a 23x30. I would like to create a dynamic chart that will 
>>> plot the date on x-axis against the sales per day on the y-axis WITH THE 
>>> LEGEND SHOWING WHICH PRODUCTS WERE MANUFACTURED. As the chart depends on the 
>>> table my graph can have 1 to 23 lines on it, with 1 to 23 items in the 
>>> legend. My table starts in cell AP5. 
>>>
>>> How can I create such a dynamic chart and legend? 
>>>
>>> Thanks
0
jonNO1 (306)
9/12/2009 5:18:08 PM
Hi,

I will have one table for each month, each with 23 potential products which 
I need to hide/show depending on whether they were sold that month.  In total 
that means for the tables and charts I need around 300 columns.  In excel I 
have 230 or whatever it is.

I need the chart for the month in question to update when new data arrives.  
The previous and future months I dont need anything.  Previous months I can 
delete the data, BUT I NEED TO KEEP THE CHART.  Future months I need nothing. 
 The reason I say to delete that data, is at least that would free the space 
for generating the data for the month in progress.

Any idea how I can get this to work?

"Jon Peltier" wrote:

> You can't hide partial rows or columns. What you can do is prevent the 
> charts from changing shape.
> 
> Classic Excel:
> Format Chart Area, Properties tab, Move but Don't Size With Cells
> 
> Excel 2007:
> Chart Tools > Format ribbon tab, click little thingie at the bottom 
> right of the Size group, Properties tab, Move but Don't Size With Cells
> 
> - Jon
> -------
> Jon Peltier
> Peltier Technical Services, Inc.
> http://peltiertech.com/
> 
> 
> 
> LiAD wrote:
> > Nice trick!!!
> > 
> > Simple but effective.
> > 
> > As a lead on from this - I have 12 tables all in a line and would like my 
> > charts to be beside the table.  If I hide full columns I'd need the charts to 
> > be spread over a wide area to avoid hiding them so the user could never find 
> > them.
> > 
> > Is there a way of hiding part columns?  As in hide just A5-A13 etc?
> > 
> > Thanks a lot for your help
> > 
> > "Jon Peltier" wrote:
> > 
> >> Start with a fully populated table. Create a chart from this table.
> >>
> >> Use your macros to hide rows or columns that you don't want to plot. 
> >> Excel charts by default do not plot hidden cells, so hiding what you 
> >> don't want to show should do the trick.
> >>
> >> - Jon
> >> -------
> >> Jon Peltier
> >> Peltier Technical Services, Inc.
> >> http://peltiertech.com/
> >>
> >>
> >>
> >> LiAD wrote:
> >>> Hi, 
> >>>
> >>> I have a table of 23 rows (products) and 30 columns (date) which plots sales 
> >>> of different items against the date against which they were sold. Not all 
> >>> products are sold every month. Using macros I can create the table that will 
> >>> summarise the sales per day in a particular month for any items that were 
> >>> sold, deleting all empty days and product lines. So my table can be anywhere 
> >>> from a 1x23 to a 23x30. I would like to create a dynamic chart that will 
> >>> plot the date on x-axis against the sales per day on the y-axis WITH THE 
> >>> LEGEND SHOWING WHICH PRODUCTS WERE MANUFACTURED. As the chart depends on the 
> >>> table my graph can have 1 to 23 lines on it, with 1 to 23 items in the 
> >>> legend. My table starts in cell AP5. 
> >>>
> >>> How can I create such a dynamic chart and legend? 
> >>>
> >>> Thanks
> 
0
LiAD (16)
9/14/2009 1:13:01 PM
You could put each month's data onto its own sheet to make it fit.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/



LiAD wrote:
> Hi,
> 
> I will have one table for each month, each with 23 potential products which 
> I need to hide/show depending on whether they were sold that month.  In total 
> that means for the tables and charts I need around 300 columns.  In excel I 
> have 230 or whatever it is.
> 
> I need the chart for the month in question to update when new data arrives.  
> The previous and future months I dont need anything.  Previous months I can 
> delete the data, BUT I NEED TO KEEP THE CHART.  Future months I need nothing. 
>  The reason I say to delete that data, is at least that would free the space 
> for generating the data for the month in progress.
> 
> Any idea how I can get this to work?
> 
> "Jon Peltier" wrote:
> 
>> You can't hide partial rows or columns. What you can do is prevent the 
>> charts from changing shape.
>>
>> Classic Excel:
>> Format Chart Area, Properties tab, Move but Don't Size With Cells
>>
>> Excel 2007:
>> Chart Tools > Format ribbon tab, click little thingie at the bottom 
>> right of the Size group, Properties tab, Move but Don't Size With Cells
>>
>> - Jon
>> -------
>> Jon Peltier
>> Peltier Technical Services, Inc.
>> http://peltiertech.com/
>>
>>
>>
>> LiAD wrote:
>>> Nice trick!!!
>>>
>>> Simple but effective.
>>>
>>> As a lead on from this - I have 12 tables all in a line and would like my 
>>> charts to be beside the table.  If I hide full columns I'd need the charts to 
>>> be spread over a wide area to avoid hiding them so the user could never find 
>>> them.
>>>
>>> Is there a way of hiding part columns?  As in hide just A5-A13 etc?
>>>
>>> Thanks a lot for your help
>>>
>>> "Jon Peltier" wrote:
>>>
>>>> Start with a fully populated table. Create a chart from this table.
>>>>
>>>> Use your macros to hide rows or columns that you don't want to plot. 
>>>> Excel charts by default do not plot hidden cells, so hiding what you 
>>>> don't want to show should do the trick.
>>>>
>>>> - Jon
>>>> -------
>>>> Jon Peltier
>>>> Peltier Technical Services, Inc.
>>>> http://peltiertech.com/
>>>>
>>>>
>>>>
>>>> LiAD wrote:
>>>>> Hi, 
>>>>>
>>>>> I have a table of 23 rows (products) and 30 columns (date) which plots sales 
>>>>> of different items against the date against which they were sold. Not all 
>>>>> products are sold every month. Using macros I can create the table that will 
>>>>> summarise the sales per day in a particular month for any items that were 
>>>>> sold, deleting all empty days and product lines. So my table can be anywhere 
>>>>> from a 1x23 to a 23x30. I would like to create a dynamic chart that will 
>>>>> plot the date on x-axis against the sales per day on the y-axis WITH THE 
>>>>> LEGEND SHOWING WHICH PRODUCTS WERE MANUFACTURED. As the chart depends on the 
>>>>> table my graph can have 1 to 23 lines on it, with 1 to 23 items in the 
>>>>> legend. My table starts in cell AP5. 
>>>>>
>>>>> How can I create such a dynamic chart and legend? 
>>>>>
>>>>> Thanks
0
jonNO1 (306)
9/14/2009 5:56:52 PM
Reply:

Similar Artilces:

Third party Cash Book Modules that work with Dynamics GP
Can anybody recommend a third party cash book module that works with Dynamics GP Version 10.00. Specific features I am looking for is the ability to enter transactions with different dates as a multi line journal, also instant cash book balance information when entering journals. -- Best Regards You've probably already looked at this but there is a moduled called something like Cash Book Management. It wasn't for the USA so I haven't looked at it. Take a look at the documentation, I think there is a user manual for it. "mhaynes" wrote: > Can anybody r...

CRM Dynamics Domain
I have in my posssion the following domain: www.crmdynamics.com I am ready to sell it. Make your best offer at : crmdynamics@gmail.com . I bid $0.01. -- Matt Parks MVP - Microsoft CRM "CRM Chief" <CRMChief@discussions.microsoft.com> wrote in message news:0F150CC7-6A73-4B4B-BB76-3DA5B7282949@microsoft.com... I have in my posssion the following domain: www.crmdynamics.com I am ready to sell it. Make your best offer at : crmdynamics@gmail.com . ...

DYNAMICS trx log backup failed
Hello: Since neither the Event Viewer app log nor the SQL 2005 log file would give me any information as to why, is there a good reason why the transaction log backup for the DYNAMICS database would not succeed? The company database transaction log backups succeed. Why not DYNAMICS? Yes, it's at the Full recovery model! SQL 2005 Standard Edition here is not on service pack 2. These plans were not created with the wizard. The plan is using "Target Server Connection" instead of "Local Server Connection" Is that a problem? childofthe1980s Disregard. I fi...

Auto Logout of Dynamics GP
Several customers have reported that users are not logging out of Dynamics GP when they leave for the day so they would like to be able to set a trigger that would automatically log a user out of the application if there is no activity for a specified amount of time ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then ...

Add primary key dynamically to a subform within subform from the
Hi, I have a subform whose primary purpose is to serve as a frame to hold another two embedded subforms at the same level. the second level subforms(continuous forms) has no linking fields with the First Level Subform but has referential integrity with the Main form's recordsource. so, when the subform loads, the primary key of the Main form is to be supplied to the Second Level Subforms so that they display records pertaining to that ID. How can i do this? I look forward to your help, friends. -- from chanakya That's what the LinkMasterFields and LinkChildFields o...

Merge & Sort Dynamic Lists w/ Data Validation
First off, let me say thanks to the Excel Usenet community that has helped me over and over through these newsgroups. You are phenomenal. Question 1: I need to create a BOTH box that will dynamically pull names from both the "Chicago Office" & "Seattle Office" columns in my sheet. It is extremely preferable to do this without VB Script if possible to avoid the security warnings on opening the sheet. (Our IT department will not budge on this..) Detail: I have two lists of employees. Column A lists the Chicago Office employees. Column B lists the Seattle Office employee...

Migration / Upgrade from CRM .3 0 to Dynamics CRM Online ?
I have a query. Please suggest some better ideas of performing this. Initially my organization had implemented the CRM 3.0 Version. It had used the CRM 3.0 for an about year.Now they are planning to move CRM 4.0 Online (CRM live hosted by Microsoft). How to upgrade from CRM .3 0 to Dynamics CRM Online? Can we do normal upgrade as we do for On-Premise/IFD versions version? As per my knowledge Microsoft does not support this kind of upgrade for CRM online version? Do we need to use the Data Migration tool to do the upgrade? thanks Sharan Gouda The Data Migration Manager will be of ...

Check Dynamic Named Range
Is there an easy way to check the actual range being defined by a dynamic named range? Hi In Name Manager, activate 'Refers' To field for this name - according worksheet is activated with actual range marked (dashed border). Arvi Laanemets "Code Numpty" <CodeNumpty@discussions.microsoft.com> kirjutas sõnumis news: 5A63CFF9-6F00-41B9-B4FE-36B51A3DC5CD@microsoft.com... > Is there an easy way to check the actual range being defined by a dynamic > named range? Press F5 (GoTo) and type the name in the box (or select from the list) and ...

Impossible to download controls for Project Web Access
Hello, I'm using IE7. On one of my workstation connected to MS Project Server, when I try to display a page with a grid, I get the error message: ------------------------------ Impossible to download controls for Project Web Access. To download them, proceed as the following: � Check that your security settings defined in Internet Explorer allow the download of ActiveX controls � Check that Internet Explorer has finished the ActiveX controls install. To install controls, in the information bar, click on "Install the active. � To download and ...

Buy Dynamics GP now and get the new .NET-based when GP,AX,SL combi
I have just received my Dynamics GP evaluation kit. I am helping a client look at Dynamics GP to replace their current accounting software. The expected concurrent user count is 25-35 depending on how many can use Portal only. The 'Reasons to Purchase' brochure in the evaluation kit talks about Dynamics GP support guarantee which is good for at least five years allowing my client to upgrade to future versions. I know that Microsoft is working on combining GP, AX, SL, NV all into one source code based on .NET and SQL Server. Will my client be able to upgrade to this new .NE...

Dynamics GP Service Pack 2
A quick question, where can I find a listing on what Service Pack 2 addressed -- Clay Tait Tait Distributors Clay, Here is a link to the KB Article that lists all the fixes: https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?WTNTZSMNWUKNTMMYKORSXPPNNWUNVVRWNXTZTOWVKTPRTTXYRMZZSMYZZLTXZXMY -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "Clay Tait" <ClayTait@discussions.microsoft.com> wrote in message news:2566B6AF-AA31-4DDE-BCB1-5FB39880E0A0@microsoft.com... >A quick question, > > where can I find a listing on what Service Pack 2 addressed ...

Additional series without legend
Excel 2003. I have a bar chart of several series. I wish to add a corresponding "goal line" series (not trend lines) for each data series (i.e. a set of "over/under" references). However, I do not want these additional data points to display in the chart's Legend. Here is a simplified notion of what I'm writing about. hope the picture works: o ===o==o============= o o o o o o o Legend o - Current Returns So far, I've thought of takin the actual legend and tweaking it in Paintbrush to remove the extra legen...

Need to void a customer payment in Dynamics
This is a basic function to every accounting system I've used for the last 25 years. How is it possible that the programmers of Dynamics missed it? Once a payment has been applied to an invoice, it can NOT be unapplied, EFT'd, or otherwise corrected. I find the shortcomings of this program increasingly frustrating. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the...

Changing the contents of a cell... dynamically?
I am wanting to do something that I don't know if it is possible. Here is the scenario... Say cell A10 gets data (numbers) entered into it, for this example lets say 100, after the focus moves from the cell I want 25 to be automatically subtracted from the number so that it is actually is 75. How can I do this if it is possible? Thanks. -- Please reply to newsgroup so we can all learn from you knowledge. Private emails will not be answered. Friendly Indi�n Hi you can use the worksheet_change event for this kind of operation. Put the following code in your worksheet module: Priva...

lookup table to dynamically display photos
Hi folks, I have created an Excel spreadsheet that contains product data such as price, weight, case dimensions, UPC, etc... The sheet uses the VLOOKUP function, which makes reference to cells in a separate tab to display the product data in a "one-up view". The user simply selects the product name from a pop-up list at the top of the page and all of the appropriate data for that product is displayed below it. This one-up view makes an excellent sell sheet for our Sales Reps to use when making a presentation, but what they would really like is to have the a product photo diplayed on...

dynamically created subclassed CComboBox doesnot recieve CBN_SELCHANGE in Activex Control
hi, Please ignore this post if a similar one appears. I posted yesterday and the post has not appeared on the NG yet. That said, let me explain the problem : I subclassed the CComboBox and am placing it on a CToolBar derivative. Both the combobox and the toolbar are created dynamically. I wish to trap the CBN_SELCHANGE message of the combobox and do some action in there. But the combobox doesnot seem to recieve the message (more precisely, the handler is never called).All this is in a classwizard generated MFC based Activex Control. Here is what I do: 1. In the main COleControl deri...

Dynamic Chart Crashing Excel
I have 5-6 charts in my spreadsheet. I have made several dynamic... meaning the update automaticly if I add new data to my sheet. The process has worked fine on several of them. However, on one worsheet in my file, I cannot for the life of me figure out why excel crashes when I name a range in my spreadsheet or if I try to put in a named range in the source data of my chart. Here is what crashes my file. CCB is the chart categories which is the formula =OFFSET(Bills!$B$35,CLX,0,-CLX) CLX is the count of the number of items to display =COUNT($D$36:$D$63) the only problem I see is t...

Is there a posibbility to change/modify the Legends
Could we customise the Chart legends without changing the Data Series ? Hi, No, the legend and data series will display the same formatting. Depending on the chart type you could add extra data series with no actual data. Format the legend entries as required and then delete the legend entries of the actual data. Cheers Andy Ash wrote: > Could we customise the Chart legends without changing the Data Series ? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Depends on what exactly you want to change. Can you explain what yoru trying to do? "Andy Pope" wrote:...

How to change the value inside Legend fields?
Everytime I print the Reprint Statements from Tools > Routines > Sales the "**Reprint**" text will printed at the report header. How can I change this text to some other text like "Repeted Document" ? I knew this text is came from "Legend[1]" field. How can I modify the value in Legend fields? Please Advise. Thanks. Although you cannot change the legend, you can use a calculated field in RW - if legend1 = "**Reprint**" then "anything you want" I usually just remove or hide that field HS ----------------------------------------...

Dynamic range w/in formula in macro
Hello, I have been trying to figure out if there is a way to allow a dynamic range within a formula in VBA. I have this static range, but the end row could change as data is added/deleted: Range("K11").Select ActiveCell.FormulaR1C1 = "=COUNTIF(R[-9]C[-6]:R[18]C[-6],"""")" I'd like to be able to change the R[18] to whichever row the user would put into an input box at the beginning of the macro. Thanks! -- Trixie ~TRIXIE ------------------------------------------------------------------------ Trixie's Profile: 438 ...

Format a data point without changing the legend
I want to format a data point of a series in excel but when I do that the legend changes completely. For example: all data points are from "series 1" in the legend. When I format one data point from this series, then the legend no longer displays "series 1" but instead it gives the X-values of all data points. How can I make sure that the legend will still display "series 1" instead? The thing is that in all my previous scatter-plots, I am able to change a data point and still have "series 1" in the legend. I do not understand why it is changed ...

Add Dynamics user to SQL Security
Have a user who is defined with Dynamics security ok but messed up in SQL security. Need to know what I have to do in SQL to get the user able to logon ok. What sql logon do I create, user in what databases, schema, etc. Do not want to delete from Dynamics tables and readd as that will force to user to rebuild all the shortcuts they have defined. One thing to try would be to remove the user's access from each of the companies then add them back, that will remove the SQL user from the company database then re-add them, shortcuts are maintained in the Dynamics DB. If that doesn...

Dynamic charts in VBA 6.3
Hi I want to write a code in VBA in which a dynamic set of rows ( the number of columns is fixed to 4) in excel are to be read and a plot type: " xlXYScatterSmooth" Could someone help me figure out how to write this one? thanks ...

Historical Stock Status As of Date
This is a multi-part message in MIME format. ------=_NextPart_000_00A5_01C8845C.ADFC9B50 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I'd like to know whether anyone has encountered an issue wherein the As = of Date that is printed in the Historical Stock Status Detailed by Item = does not follow the date as indicated in the report options screen. = Currently we are trying to print the said report but no matter what date = we put in, the As of Date that prints on the report is set to 30 Jan = 2008. Is there anything that we...

Cashbook Bank Management Default Document on Dynamics GP 9 SP3
Hi there We applied Dynamics SP3 recently from SP2. The problem we experience relates to the way the incremental numbering works in SP3. Previously, the next document number was derived from the Cashbook Setup tables. When a user changed the number in the setup, the system would proceed with that number and continued incrementing the number as defined in the setup table. What happens with SP3 is Dynamics now looks into CB600066 for the next number. Some of our sequences starts with '00', others with '5', some others with '4'. The most recent sequence, the one we want...