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/200...ynamic-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
_______


wrote in message
...
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.






On Nov 3, 11:02 am, "Jon Peltier"
wrote:
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, MicrosoftExcelMVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

wrote in message

...
I've seen examples, but every time I try to duplicate them the entries
are rejected byExcelchart 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

On Oct 17, 3:30 am, Rob wrote:

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

"Jon Peltier" wrote:
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, MicrosoftExcelMVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______

"Rob" wrote in message
...
Sorry Jon but it isn't working for me.... I'm still getting 200
lines
of
info
in mycharts. 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" wrote:

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

2. I would use adynamiccharting approach, which accounts for a
changing
range of data:
http://peltiertech.com/WordPress/200...ynamic-charts/

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

Name: TheMinimum
Refers To: =5

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

Use VertValues2 in the chart rather than VertValues.

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

"Rob" wrote in message
...
I'm looking for aDynamicmeans 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. Myproblemis 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.