Maintenance Schedule

[qandiofficer]

Hi there,

I'm relatively new to Excel and I have created a Workbook with several tabs holding all our equipment, their service/PAT test dates and conditioned the cells to turn either red/yellow/green dependent on whether or not the date is satisfactory. As there are a large amount of equipment to keep track of I was wondering if it was at all possible to create a new tab which will display a collation of yellow/green cells in order to see immediately what needs action without trawling through all of the tabs? I may be being too optimistic but worth a shot


Thank you!

 

[oldbrewer]

I would only use one sheet for all data - easy then to search via a pivot table - ie all drills that have yellow or green dates

if you add a column to group equipment eg hand tools, power tools, testing equipment etc you will get overviews as well if you want them....

 

[qandiofficer]

Ah I would have done that only I'm passing this sheet on to somebody else to maintain who's IT skills aren't up to par and I think they would find it slightly complicated if it was on one sheet unfortunately!

 

[oldbrewer]

so they can cope with several tabs but not one tab.......

 

[oldbrewer]

[TABLE="width: 1071"]
<colgroup><col><col><col><col><col><col span="6"><col><col span="2"></colgroup><tbody>[TR]
[TD]item[/TD]
[TD]previous test date[/TD]
[TD]test freq (days)[/TD]
[TD]next test due[/TD]
[TD]date carried out[/TD]
[TD]status[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]item1[/TD]
[TD="align: right"]01/04/18[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]01/05/18[/TD]
[TD="align: right"]29/04/2018[/TD]
[TD]tested[/TD]
[TD]#####[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]today's date =[/TD]
[TD="align: right"]14/06/2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]item2[/TD]
[TD="align: right"]04/04/18[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]03/06/18[/TD]
[TD][/TD]
[TD]red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]item3[/TD]
[TD="align: right"]07/04/18[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]07/05/18[/TD]
[TD="align: right"]13/05/2018[/TD]
[TD]tested[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]item4[/TD]
[TD="align: right"]10/04/18[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]09/06/18[/TD]
[TD][/TD]
[TD]red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]green[/TD]
[TD]ok[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]item5[/TD]
[TD="align: right"]13/04/18[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]13/05/18[/TD]
[TD="align: right"]13/05/2018[/TD]
[TD]tested[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]yellow[/TD]
[TD="colspan: 2"]within 2 weeks[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]item6[/TD]
[TD="align: right"]16/04/18[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]15/06/18[/TD]
[TD][/TD]
[TD]yellow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]red[/TD]
[TD]overdue[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]item7[/TD]
[TD="align: right"]19/04/18[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]19/05/18[/TD]
[TD="align: right"]21/05/2018[/TD]
[TD]tested[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]item8[/TD]
[TD="align: right"]22/04/18[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]21/06/18[/TD]
[TD][/TD]
[TD]yellow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]item9[/TD]
[TD="align: right"]25/04/18[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]25/05/18[/TD]
[TD][/TD]
[TD]red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]item10[/TD]
[TD="align: right"]28/04/18[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]27/06/18[/TD]
[TD][/TD]
[TD]yellow[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]######[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]=IF(E2<>"","tested",IF($L$2-D2>0,"red",IF(D2-$L$2<15,"yellow","ok")))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]easy to use the colors in column F to drive conditional formatting[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]