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.

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/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 the dynamic Y 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, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


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

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" wrote:

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/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 the dynamic Y 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, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


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

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

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/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 the dynamic Y 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, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


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

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, Microsoft Excel MVP
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 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" wrote:

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/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 the dynamic Y 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, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


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

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.

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.

--
Thanks,
Shane Devenshire


"Rob" wrote:

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.

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?
--
Thank you,

Jennifer


"ShaneDevenshire" wrote:

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.

--
Thanks,
Shane Devenshire


"Rob" wrote:

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.

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


"Jennifer" wrote in message
...
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?
--
Thank you,

Jennifer


"ShaneDevenshire" wrote:

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.

--
Thanks,
Shane Devenshire


"Rob" wrote:

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,
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.
--
Thank you,

Jennifer


"Jon Peltier" wrote:

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


"Jennifer" wrote in message
...
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?
--
Thank you,

Jennifer


"ShaneDevenshire" wrote:

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.

--
Thanks,
Shane Devenshire


"Rob" wrote:

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.