Never mind. Got it.

Once again thank you
--
when u change the way u look @ things, the things u look at change.


"Jon Peltier" wrote:

I thought you only wanted one series in the chart. Delete the duplicates.

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


"sahafi" wrote in message
...
Just noticed that when selecting an item from the list the chart plots 2
lines on top of each other. When I hover the mouse over the tick mark, it
will say series2 and on the line will say series1 and that for all 12
items
even though I have series names from series1 to series12! When I click on
the
line the formula will say: =SERIES("Series2",,Model!ChartData2,13) and
that
for all 12 items as well.. exactly the same forumla. ChartData is my name
range for 'Y' and weeks is my name range for 'X'. I have changed all 12
formulas for 'Y' series as: =OFFSET(weeks,0,Model!$S$64) S64 is my link
cell. Also when collapsing the textbox for the 'Y' value under source
data,
all 12 lines will point to one same column, but the values on the chart
are
actually correct for all 12 items!! I probably messed up somewhere... any
idea?

Thanks.
--
If u change the way u look @ things, the things u look at change.


"Jon Peltier" wrote:

Your dropdown box has 12 items, so assuming a Forms toolbar dropdown,
your
linked cell has a value from 1 to 12. Assuming the X is in one column,
the
12 Ys in the next 12 columns, and the linked cell to the dropdown is cell
J1, change your Refers To formula for Y to this:

Name: ChartValues
RefersTo:
=OFFSET(ChartCategories,0,'Model'!$J$1)

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


"sahafi" wrote in message
...
Jon, while your method does work as is, it doesn't fit my design.
Meaning,
your explanation of creating 12 data series will plot 12 line on the
chart,
which is not what i'm after. Remember I mentioned I have a combobox
with
dropdown list from which a user will select one item out of the 12, and
they
should see a single line for that item. Showing 12 lines week by week
makes
the chart too busy, besides, i'm really ploting products growth %.
There's
no
growth relationship between the products themselves, so no sense
plotting
all
of them at once. Is there a way to accomplish this dynamically and
having
the
dropdown list working at the same time?

Thanks.
--
when u change the way u look @ things, the things u look at change.


"Jon Peltier" wrote:

Here's the formula i'm trying to use to help plot only the data and
ignores
#N/A which currently it didn't
=OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1)

I hope this is
=OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1)
and that B1:B65 are all filled.

Is this the refers-to formula for a named range? You should have one
range
for X and one for Y for each series in the chart (though the X can be
used
by multiple series).

Named range for X values:
Name: ChartCategories
RefersTo:
=OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B)-65,1)
Better:
=OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B66:$B11 7),1)

Named range for first set of Y values:
Name: ChartValues1
RefersTo:
=OFFSET(ChartCategories,0,1)

Named range for second set of Y values:
Name: ChartValues2
RefersTo:
=OFFSET(ChartCategories,0,2)

etc.

Use
='Model'!ChartCategories
='Model'!ChartValues1
for the X values and Y values for the first series,

='Model'!ChartCategories
='Model'!ChartValues2
for the X values and Y values for the second series, etc.


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


"sahafi" wrote in message
...
Jon,
First my list is populated from another report list on the same
sheet.
My
chart list is as follow:
Weeks on column B (B66:B117) while my heading/labels start from C65
to
N65.
I have this formula on O65 =OFFSET(B65, 0,$S$64) S64 is my link
cell.
On
C66 I have this formula =IF(D7=0,NA(),D7) and copied down C66:N117
this
formula basically looks the above report list and populate the data
while
substitute any zero values with #N/A. Up to this point my chart is
fine
and
the combobox is working fine. Currently my list is showing actual
data
up
to
week 16 and #N/A thereafter.
Here's the formual i'm trying to use to help plot only the data and
ignores
#N/A which currently it didn't
=OFFSET('Model'!$B$66,0,0,COUNTA('Model'!$B:$B,-65),1), I have
similar
formula based on the values on column O, as my 'Y' axis values.
Model
is
my
sheet where the report list chart list and the chart reside.


Thanks.
--
when u change the way u look @ things, the things u look at change.


"Jon Peltier" wrote:

It would help to see your formulas.

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


"sahafi" wrote in message
...
I have a dynamic line chart with 12 data series that each contains
weekly
data for 52 weeks, and set up as a combo box chart. Currently I
have
data
for
only 16 weeks, while the rest of the year is howing zero values.
I
changed
the zero to #N/A to avoid plotting that on the graph, which
worked
fine,
but
the issue is the X axis. I have tried 2 options but neither
produced
what
I
needed.
* I have used a formula to show the weeks as N/A if no data
available
on
the
next column.
** I used the OFFSET formula to creat a range, but both options
plotted
the
#N/A on the X axis. How can I incorportate a range successfully
in
another
OFFSET formula (combo box with dropdown list) to plot only the
cells
with
data. Any help is greatly appreciated.
--
when u change the way u look @ things, the things u look at
change.