One horizontal axis for 3 sets of data

[rosshkerr]

Hi Guys

i have sheet that we use for carry out an audit. We are just changing from daily audits to one per shift. I am trying to get the horizontal axis to show one date but for the 3 shifts. It is hard to explain but when i enter data for shift 2, for example, it wont show on the chart until data is entered for, say, shift 2! I have tried nan if or formula but am not that great on formulas. I have attached the file just in case anyone has time to have a loook

Thanks in advance

Ross

[Andy Pope]

Try using row 7 for the category.

[rosshkerr]

Hi Andy. The shifts rotate so, for example, shift 1 will be on nights one week, lates the next and mornings the week after so the data isn't entered in the same order, it depends on who is working which shift! What i am after, and have tried without success (i am not greta on formulas, more of a macro person) is to say if C1 is empty put C7 and if C7 is empty put C13 so where ever the data is entered this formula will always show a date. Sorry if this isn't very clear

Regards

Ross

[Andy Pope]

You could construct the required dates on a separate row using formula like this

=IF(B1="",IF(B7="",IF(B13="",NA(),B13),B7),B1)

Or you could change the chart to xy-scatter and then give each shift series it's own x and y values

[rosshkerr]

Hi Andy

The formula seems to work really well, and it highlights when an audit hasn't been done on a particuar shift which could be useful. I will try this and see how it goes (will also try the scatter chart as I have never made one before)

Many thanks

Ross