Growing/Shrinking/Selective Chart Data

[Rob]

I'm looking for a Dynamic means of populating a bar chart.

Cloumn A (Rows 2-200) Has the Names (Y Axis) of each Item and Cloumn B (Rows
2-200) (X Axis) has the Number Values. My problem is twofold... One, every
week the Y axis list can grow or shrink and Two, I only want to show those
that have an X Axis value that is greater than five. Anything less than or
equal to five I would like to have hidden from showing in the chart.

Is it possible to have some sort of way or formula that can make this
possible in a chart or in pivot table?

Thanks in Advance.

 

[Jon Peltier]

1. First, put the X values to the left of the Y values. It's not strictly
necessary, but Excel by default uses the left column for X.

2. I would use a dynamic charting approach, which accounts for a changing
range of data:
http://peltiertech.com/WordPress/2008/05/14/dynamic-charts/

3. I would deal with the condition of plotting only Y>5 by defining another
name. In my example I used a name "VertValues" for the dynamic Y value
range. I would define these names:

Name: TheMinimum
Refers To: =5

Name: VertValues2
Refers To: =IF(VertValues>TheMinimum,VertValues,NA())

Use VertValues2 in the chart rather than VertValues.

- Jon

 

[ShaneDevenshire]

Hi Rob,

Since time began we have used dynamic range names to handle this problem,
however, as of 2003 there is an alternative which works if you data is in
columns as your's appears to be.

Suppose the data is in the range A1:B200. Highlight the range and choose
Data, List, Create List, OK. (in 2007 lists are called tables)

Now create your chart from the list range. If you add new rows of data the
chart will automatically include them as long as they are directly adjacent
to the list (below). And if you delete any rows of data in the list, it will
also adjust. There are some differences between this approach and the
dynamic range name approach, but if this does what you need it is easier to
set up.

 

[Jennifer]

Have you guys noticed that when the named range changes in size that the
chart kicks out the name and reverts back to the original selection size.
Example
Table is originally A1:B10
You name it
Create a chart and it accepts the named range
BUT
Table grows and now it is A1:B15
It kicks out the name and in the source it says Sheet1!A1:B10
What is that about?

 

[Jon Peltier]

This is true if you use a named range in the Data Range tab of the Source
Data dialog. Excel does not remember these names, but instead always
converts them to the cell addresses of the names.

If you use named ranges for the X and Y value ranges of each series, in the
Series tab of the Source Data dialog, the names are remembered.

- Jon

 

[Jennifer]

Jon,
You rock! I have spent way too long trying to get around it. Unfortunatly I
didn't think to use named ranges seperately for the X and Y axis. Sometimes
the things that stump me are so dumb. Thanks a ton.

 

[Rob]

Sorry Jon but it isn't working for me.... I'm still getting 200 lines of info
in my charts. All the formulas are working because I do not get any errors
but the chart still shows all the items instead of just what I want.

Does it make a difference the the ones I want to exclude are not in/at the
end of the series? Because they are all in between one another. Example of
the values below...

Column B
21
92
71
69
36
64
62
4
76
71
48
43
50
48
65
50
188
48
23
2
0
10

 

[Jon Peltier]

The order doesn't matter. The calculated array will be treated like this:

21
92
71
69
36
64
62
#N/A
76
71
48
43
50
48
65
50
188
48
23
#N/A
#N/A
10

I posted a simple workbook illustrating the technique I described at
http://peltiertech.com/Sample/DynoChartForRob.zip.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

 

[Rob]

THANK YOU very much Jon!!! That is just Awesome!

 

[Bob]

THANK YOU very much Jon!!! That is just Awesome!

I've seen examples, but every time I try to duplicate them the entries
are rejected by Excel chart source dialog boxes.

Has anyone been able to make the entries and get them accepted? If
so, can you post the steps you used to do it?

Thanks

 

[Jon Peltier]

Post on top like everyone else, so it's easier to read the thread.

How are you entering the entries, into which dialogs? What is the error
message you receive?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



I've seen examples, but every time I try to duplicate them the entries
are rejected by Excel chart source dialog boxes.

Has anyone been able to make the entries and get them accepted? If
so, can you post the steps you used to do it?

Thanks

 

[Bob]

In Excel 2007, I right click on the chart and then click on Select
Data and in the chart data range window I paste in the offset
formula. I click ok and then get "That function is not valid".

I've also tried to update by going into the edit series area and
pasting it in there and get the same message.

I've tried it also by typing in rather than pasting the formula.

 

[Jon Peltier]

You have to use the offset formula to define a dynamic "Name". This Name is
then used in the chart source data dialog. Follow these instructions which
show how to create a name and populate a chart with it:

http://peltiertech.com/WordPress/2008/05/14/dynamic-charts/

It's a little different in 2007. Press Ctrl+F3 to open the Names dialog, and
click Add to actually create the names.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


In Excel 2007, I right click on the chart and then click on Select
Data and in the chart data range window I paste in the offset
formula. I click ok and then get "That function is not valid".

I've also tried to update by going into the edit series area and
pasting it in there and get the same message.

I've tried it also by typing in rather than pasting the formula.