X Axis help

Especially with line and column charts where the horizontal axis is a text/category axis, I find that the easiest way to do this is to combine all of the data I want to show in the chart into a single table, then build the chart from that table. In this case, maybe a formula/function in row 27 and 28 of Current Peds that will retrieve the data from the Peds IDP tab for each time/text entry in row 1 (or N/A if that time isn't present). Then have the chart use the appropriate columns from row 27 and 28 for the line chart series.

Would you be allowed to do something like that (move/copy data around the spreadsheets), or are you required to solve the problem in the chart itself (which usually isn't very good at solving these kinds of problems).

The approach I'd adopt is to read the values from columns B&Cof the PEDS IDP sheet to cells in columns G:AG on the Curret PEDS sheet.
These values will then line up with Breaks and Meals series.

The first thing to do is creae proper time numbers in Row 1 of the Currnet PEDS sheet. At the moment they are just text. So for instance in G1 enter 9:15 and then similarly across column 1.

Then introduce two new rows. For testing I used the spare row 5 but for neatness you'll probaly want to insert two new rows.
In G5 enter the formula

Formula:

Please Login or Register  to view this content.

and copy this across to AG5. This repeats the PEDS column B values.

Add a similar formula on another row to pick up column C from the PEDS IDP sheet

Then replace the current PEDS IDP series with the values from the two new rows