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
2 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.
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.
