Forum Discussion
Excel help
Hello Excel Community.
I would appreciate some help and support if possible with Excel.
I am trying to set up a racking inspection check sheet, various aisles, bays and levels. The intention is to prioritise the severity of defects for repair/replace (Red, Amber and Green) with colour co-ordinated cell fill and record one of four types of defects against the earlier colour. Examples of defects would be, Upright, Beam, Crossbar and Pins. The first 3 defects could fall into all 3 repair priorities. Any support or guidance you could offer would be greatly appreciated. Many thanks
Example
3 Replies
Your question is quite unclear. Can you explain little more and show your input data and desired output manually. We will try to achieve that output by formula.
Hello Harun24HR and thank you for your response. very much appreciated
Today a visual inspection of the racks are carried out using a notebook, later to record into an excel. The format is very poor.
In its simplest format, for example and using the above table as a live example.
Inspection carried out starting alphabetically in Aisle A from Bay 1 to Bay 8 and will include all other aisles and bays. The Inspection is looking for defect/damage to any of the 4 components that assemble the racking (Upright, Beam, Pins and Crossbar). If any defect is identified it is recorded by severity, Red, Amber, Green (like traffic light system), to prioritise the repairs. Red being the most severe. All defects are repaired/replaced within time frames according to the severity. Hope this offers some clarity.
Again referring to the table and only as an example,
Aisle A, Bay 1 is showing a Pin defect/damage and the inspector has determined the fault to be of Red severity (most severe) importance to repair
Aisle C, Bay 3 is showing a Beam defect/damage and the inspector has determined the fault to be of Amber severity (medium severe) importance to repair.
The question is, is it possible to create an inspection record that can record a severity colour and the damaged component in the same cell ? drop downs or formulas ?
I am able to use conditional formatting to achieve one of the two requirements, but unfortunately I am inexperienced to do both. Can you help ? Any assistance again would be greatly appreciated
maybe add a number after like "Pin 3", "Pin 2" or "Pin 1" for low, med, high rank. Then the conditional formatting can use a custom formula like =Right(B3,1)="1" for Red (or you can rank them in the opposite order, whichever way makes sense for you) where B3 is the upper left corner of the range (i.e. Aisles A and Bays 1) so the Applied To range would be B3:I9 in the above example. Then repeat for "2" and "3" for Amber and "Green".
if you want to get 'fancy' you could define names like Pin1, Pin2, Pin3, Beam1, Beam2, ... in the Name Manager to be the corresponding values 1,2,3. Then you could use 'standard' conditional formatting rules and Color based on Values.
Either way, you may want to use Data Validation (list) to restrict the entry to those options and give the user a helpful dropdown. instead of listing all of them in the Data Validation window I would create the list on another sheet and just reference that list range.
