Fire Department Equiptment Database

Afternoon Guys

I am currently working on a Excel Spreadsheet to manage all our equipment at our Fire station, The idea is that it is a live document that makes us visually aware of what equipment needs maintenance.

I have set up a dashboard to view all information and have that working fine.

In the "Fire Hose" Tab i use a IF formula to look at the next test date, this then gets allocated a word based on the date: Current, Renew, Expired.

I have set up a conditional formatting for cell T2, based on what word is displaying will highlight the row.

I need help getting it set up so i don't need to do all 3 steps of conditional formatting for every single row, As i have 500 lengths of hose to enter.



I have attached the Spreadsheet, Please go to Fire hose and look at the row i have filled in, I really appreciate any help.

Thanks
Carl

Thanks Norie

One more quick question, How would i use conditional formatting so if the cells are blank they stay white?
The conditional formatting is looking for a word, so i would need to add the word blank to the existing code somewhere im guessing? this is the code that i am using to dictate "Current, Expired, Renew"

=IF(F2<TODAY(),"Expired", IF(F2-30<TODAY(),"Renew", "Current"))

Cheers
Carl

Hey Norie

So im using this code now and all seems to be working fine.

=IF(F2="","Blank",IF(F2<TODAY(),"Expired",IF(F2-30<TODAY(),"Renew","Current")))

Is there a way i cant get it to look at a different cell as well as the ones its already looking at? in one cell i type pass / fail. So i need to get it so when i type in fail in that column it adds fail to the list instead of Current, renew or Expire?

Sorry if that dosnt make sense, if u look on my spreadsheet you can see what im talking about!

No i wont be able to do it this way

Ive attached a Screen shot of what im trying to do.

=IF(F3="","Blank",IF(F3<TODAY(),"Expired",IF(F3-30<TODAY(),"Renew","Current")))

The above code is what i am using to place words into a column when it recognizes expiration dates coming up. I then use a count formula on a different tab to count the total of equipment in date, needs attention or expired. i want to add a forth column on that sheet saying FAILED.

In order for that to work i need the above formula to look in cell G2 for the word Failed, if it finds the word fail it needs to place that in the column so that the Count Formula on the Dashboard can see the word failed and count it.

i dont have any problems with the conditional formating its just getting the above code to look in G2

Thanks Norie