3-d referneces in chart series values

[DJFudd]

I have a chart that has three series plotted on it, the first is column
format and the second and third are lines. The first (column) represents
average golf scores for one hole for individuals, the second (line)
represents par, while the third (line) represents overall average for all
golfers.

I can plot the column graph for the 23 competitors - no problem. The problem
is with the two line graphs. I want the lines to extend the width of the 23
columns.

To do this I need 23 points that are the same for each line. I would like to
use something like =player1layer23!$c$323, but I keep getting an error.

I can do something like
=(player!$C$323,player!$C$323,player!$C$323,player!$C$323), but I can only
seem to be able to do this 18 times.

Is there anyway to solve this dilema?

Rudimentary I know, but this represents what I am trying to do.

There are two line which need to extend the width of the chart
There are 23 columns.
___________________________________ line 2

| | | | | | | | | | | | | | | | | | | | | | | | |
--------------------------------------------------line 1
| | | | | | | | | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | | | | | | | | |
1 1 1 1 1 1 1 1 1 1 2 2 2 2
1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3

Thanks

 

[Andy Pope]

Hi,

If all the points are the same value for each player then actually you
only need 2 occurances of the value for each line.
If you move the 2 lines series to the secondary axis, add the secondary
category axis via the Chart Options dialog. Then unset the secondary
category axis to Cross between categorys.
You will need to alter the ranged used for Category labels on the
secondary axis to 2 points.

It would also be a lot easier if you built the chart data on a separate
sheet. Consolidating the data from 23 sheets to 1 before charting.

Cheers
Andy

 

[DJFudd]

Thanks Andy . . .

I took your advice and did it the easier way. Worked like a charm.

Something that I found out though - I had the values in cells on the same
sheet as the chart, when I hide the rows so they wouldn't print the graph
lines went away.

Anyway, a minor inconvenience being that your idea worked.

Thanks again.

....Dan

 

[Andy Pope]

Hi,

That is because the option to 'plot visible cells' is on.

xl2003 - Select chart. Tools > Options > Chart. Uncheck 'Plot visible cells
only'

xl2007 - Select chart. Chart Tools > Design > Data > Select Data > Hidden
and Empty cells > Show data in hidden rows/columns.

Cheers
Andy

 

[DJFudd]

Andy . . .

Once again you hit the nail on the head. I have done as you suggested and my
lines are back.

Thanks

....Dan