Dynamic X Axis in line chart

Hi again

1. How do i change the colours of the Legend lines in a Dummy line. In the attached graph, i would like the green to be dark brown and the blue to be the lighter brown. I can't seem to find a way to change data for a line that is hidden. (someone else created the dummy line for me)

2. I would like the data values along the X axis to update as the numbers increase dynamically. To explain:
When you add values to the Data A or Data B columns, the Y axis maximum number changes accordingly. Can this be done with the X Axis?

Thanks again for any help

Craig

Thanks Andy

Hello Andy
I have tried to get this dynamic X axis to work, but it would seem that I am punching above my excel weight. I simply can’t get this to work how I would like and wondered if you could give me a foot up.
As you can see from the sample chart attached, the project is for personal (recreational) purposes and not business. The chart displays sport results between me and a friend. I enter the data from another spreadsheet, but eventually, I may try to dynamically link the two.
Anyway, here is what I am trying to achieve (I don’t even know if this is possible – it was certainly too big a challenge for me)
1. I enter the scores each week (or whenever the event is) into columns Q and R respectively.
2. These scores are worked out as an accumulation in columns T and U and illustrated in the chart.
3. As the scores increase, the labels on the Y axis increase accordingly.
4. I manually enter the date (month and year – APR 09 for example) for each event score in column P which is picked up in column S to be displayed as the label for the X axis – herein lies the problem.
5. I would really like the X axis to behave in the same way as the Y axis. The first label would be APR 09 and the last entry (at the end of the axis) should be in this case NOV 09. As I enter more data and dates, the axis will update accordingly showing the new latest date as the last item along the axis.
6. Moreover, I only need about 12 entries along the X axis and don’t want this to get crowded as I enter more and more dates and scores. The X axis would therefore have the first date in column P at the start of the axis, the last date in column P at the end of the axis and around 10 of the other dates averagely distributed in between (no duplicates).
7. Currently I have the list in column S set to 100 items, but I would like this not to be exhaustive, so I can keep adding dates and scores as they happen.
I have attached the graph as well as a PDF showing a sketch of the graph at various stages.
I am so grateful to you for looking at this for me and I look forward to your response.

Best regards

Craig Proudfoot

Hi Andy

When i open your spreadsheet to have a look, it says " . . .formula in worksheet contains invalid reference."
Do i need to add the Named Ranges first for this to work?
Forgive me if i am being stupid - i used to think i could do anything with Excel, now i know there is a whole other level of expertise. Charting could almost be an autonymous Microsoft package.

Cheers

Hi

Me jumping the gun
I opened the spreadsheet directly and it didn't work
when i saved it, and then opened it - it worked
Lesson learned

There seems to be a bit of congestion at the end of the X axis if i enter new scores and dates - anyway to avoid this

C