Hello all,

I'm looking for suggestions on the best way to go about getting the
information I want to display in a chart. Depending on the response, I may
also need help implementing the suggestion. Here's what I want to do:

On Sheet3 I have a range of data, for example:

Columns
A B C D
20-Sep R Y G
21-Sep Y n/a R
22-Sep n/a G Y
23-Sep R R n/a

On Sheet7, I want to have 16 line graph charts that:
1) X axis = Column A

2) Y axis to be a label that says, "Red, Yellow, Green" but the actual data
to be based on the range of Column B, C, D, etc.

3) In my example above, I have 3 columns of data. In my real spreadsheet
there are 16 columns of data. I will have a separate chart based on each
column.

So for example,
one line chart with the X axis = Column A and Y axis = Column B,
then a second chart with the X axis = Col A and Y axis = Col C, etc.

But, I only want to plot dates with R, Y, or G. If there is an N/A in
Column C, I don't want to plot that date in the line chart. Likewise for the
rest of the charts. So based on my sample data above, I would have my first
chart like this:

X axis = Column A
Y axis = Column B
Y axis label = "Red, Yellow, Green"

Green |
|
Yellow | x
|
Red | x x
_____________________________________________
9/20 9/21 9/23

Here's what I'm currently doing:

On Sheet3:
In column BA I have the formula =A1 to get the date.
In column BB, BC, BD, etc. I have the formula
=IF(B3="R",1,IF(B3="Y",2,IF(B3="G",3,"")))

On Sheet7 I have a line chart:
The Y axis = scale 1-4 with Word Art placed at Red = 1, Yellow = 2, and
Green = 3.
The X axis = Sheet3!BA1:BA400
The first series = Sheet3!BB1:BA400

This plots the dates on the X axis and if a date has a G, then it plots the
marker for the date at the Y axis 3 gridline, if the date has a Y it plots it
at the 2 gridline, or if it has a R, it plots the mark at the 1 gridline.
But if there was an n/a, from the original data set, my IF statement ignores
that and leaves a blank in the range. Then when the chart sees a blank, it
plots a 0 for that date and my line graph takes a dive. So I want to skip
the dates with blanks or n/a's from the original data set in my graph.

Is there an easy modification to what I'm currently doing or is there just
simply a more efficient way to go about this? I'm a little afraid to create
a chart in VB since I don't have any experience doing that, but not to say if
someone told me how to do that I wouldn't try it. :)

Thanks for any input,

Sharon