Impossible - Dynamic Legend?

LiAD · External Since: Jan 08, 2009 Posts: 15

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

Jon Peltier · External Since: Feb 01, 2004 Posts: 179

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

LiAD · External Since: Jan 08, 2009 Posts: 15

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
>

Jon Peltier · External Since: Feb 01, 2004 Posts: 179

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

LiAD · External Since: Jan 08, 2009 Posts: 15

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
>

Jon Peltier · External Since: Feb 01, 2004 Posts: 179

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