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]

item

previous test date

test freq (days)

next test due

date carried out

status

item1

01/04/18

30

01/05/18

29/04/2018

tested

#####

today's date =

14/06/2018

item2

04/04/18

60

03/06/18

red

item3

07/04/18

30

07/05/18

13/05/2018

tested

item4

10/04/18

60

09/06/18

red

green

ok

item5

13/04/18

30

13/05/18

13/05/2018

tested

yellow

within 2 weeks

item6

16/04/18

60

15/06/18

yellow

red

overdue

item7

19/04/18

30

19/05/18

21/05/2018

tested

item8

22/04/18

60

21/06/18

yellow

item9

25/04/18

30

25/05/18

red

item10

28/04/18

60

27/06/18

yellow

######

=IF(E2<>"","tested",IF($L$2-D2>0,"red",IF(D2-$L$2<15,"yellow","ok")))

easy to use the colors in column F to drive conditional formatting

<colgroup><col><col><col><col><col><col span="6"><col><col span="2"></colgroup><tbody>
</tbody>