Spreadsheet Formulas/Layout

[mjbrant0]

https://imgur.com/a2wvVav

I'm trying to create an equipment log to monitor our facility's equipment and their various conditions. The imgur link is what I have so far. If anyone can suggest a cleaner layout, I'd be open to it.

• Number of Repairs-I'd like to color code each cell in this column based off the number of repairs. 0-1=green, 2-3=yellow, 3+=red. I'm looking for the formulas that would allow me to do this. Also, for repair 1 and repair 2, can anyone suggest a more attractive/practical way to show how many repairs there have been? They don't have to be separate columns and obviously not every unit will have the same number of repairs. Maybe some sort of expandable/collapsible solution where I could see a number and brief description of each event.
• I'd like to do something similar with the Current Age/Lifespan columns. If the current age is 3+ years under under the lifespan=green, 2-1=yellow, 0 or over=red. I'm also not sure how to properly write this formula.

Any help is appreciated!

 

[adam087]

Hello

There are probably several ways to look at this, depending on how complicated you want it to be. One of the considerations I would have would be how many pieces of equipment are you monitoring? But here are some general thoughts.

As you've identified, using columns for repair 1,2,...,n etc can be problematic - you'll be cursing yourself when something breaks down for the fiftieth time .

With that in mind I would move the repairs to another sheet then use a =COUNTIFS formula to bring the count to the main page. I'd lay the second sheet out as
ITEM | DATE | REPAIR
and create a new line for each repair. Potentially with another column for REMARKS so you could make a longer comment on what was done.

If you want to see details of what repairs were done on each piece of equipment then probably a PivotTable from the repair sheet would be the best way of doing this.

The 'age' is probably best done by putting in a 'new' date then calculating from =(TODAY()-[age])/365.25.

All the colourcoding can be done with Conditional Formatting but if you need any specific advice on that do ask.

/AJ

 

[mjbrant0]

I'm not skilled in excel at all. Exactly what =countifs formula could I use to reference the other sheet?