Setting logic to show fields from a list of drop-down selection means that upon selection of an item, a related text in the display cell shows. These can be useful especially for:
Conditional display of fields
Data validation
Contexual input
In tables (either a Default or Prefilled table), you can set cells as a 'List' field. Similar to a 'Dropdown' list, you can link it to an existing list or manually type the list. To begin, set one or other column as your 'List' Fields. You can format your table however you wish.
To populate information, use the formula IFS():
=IFs(Condition1, Value1, Condition2, Value2, Condition3, Value3, Condition_n, Value_n)
To populate information from multiple drop-down selections, nest both IFS() and AND() formulas:
=IFs(AND(Condition1, Condition2), Value1, AND(Condition3, Condition4), Value2, AND(Condition5, Condition6), Value3, AND(Condition_x, Condition_y, Value_n)
Example:
For a default table
Given that you have a Standard Operating Procedure with a default table register of Work Activity/Trade and their identified hazards.
You have this table to determine the LEVEL of the hazard. (For example if the LK = A and CQ = 1, meaning the level is H)
You want to automatite the LEVEL results based on the dropdown/list selections for LK and CQ. You opt for these to be list fields to avoid errors.
Before filling out the formula fields, it is advisable to create a guide so that it's easier for you to create the formulas. (Example below is not the full list of conditions and values. It only shows the first column value of the hazard table.)
Guide:
1st Condition | 2nd Condition | Value |
LK = A | CQ = 1 | H |
LK = B | CQ = 1 | M |
LK = C | CQ = 1 | L |
LK = D | CQ = 1 | L |
LK = E | CQ = 1 | L |
Use the IFS() formula in the display cell B1:
=IFs(AND(LK="A", CQ="1"), "H", AND(LK="B", CQ="1"), "M", AND(LK="C", CQ="1"), "L", AND(LK="D", CQ="1"), "L", AND(LK="E", CQ="1"), "L", 1=1, "")
There should be one true value in the conditions, therefore use '1=1, ""'.
Work Activity | Potential Hazards Identified | LK | CQ | LEVEL | Risk Control |
|
| (C1 - list field) | (D1 - list field) | =IFs(AND(LK="A", CQ="1"), "H", AND(LK="B", CQ="1"), "M", AND(LK="C", CQ="1"), "L", AND(LK="D", CQ="1"), "L", AND(LK="E", CQ="1"), "L") |
|
Default tables don't have a fixed number of rows when you're creating a template. Your formulas will reference columns, so each time you add a new row in filling out your form, the same formula applies to each cell in that column.
For a prefilled table
Suppose you have an equipment register and you want that upon the selection of an equipment, their asset number automatically displays in the next column. There is a limit on the number of equipment used and inspected in a day.
Given that this is your data set,
Vacuum | 01 Vacuum |
Grinder | 01 Grinder |
Ext Lead 1 | 04 Lead |
Ext Lead 2 | 05 Lead |
Polyvac | 10 P. Vac |
Mixing Drill | 19 M Drill |
Use the IFS() formula in the display cell B1:
=IFs(A1="Vacuum","01 Vacuum",A1="Grinder","01 Grinder",A1="Ext Lead 1","04 Lead",A1="Ext Lead 2","05 Lead", A1="Polyvac", "10 P. Vac", A1="Mixing Dirll","19 M Drill", 1=1, "")
There should be one true value in the conditions, therefore use '1=1, ""'.
Equipment | Asset No. | Inspection Date | Signature |
(A1 - list field) | =IFs(A1="Vacuum","01 Vacuum",A1="Grinder","01 Grinder",A1="Ext Lead 1","04 Lead",A1="Ext Lead 2","05 Lead", A1="Polyvac", "10 P. Vac", A1="Mixing Dirll","19 M Drill", 1=1, "") |
|
|
(A2 - list field) | =IFs(A2="Vacuum","01 Vacuum",A2="Grinder","01 Grinder",A2="Ext Lead 1","04 Lead",A2="Ext Lead 2","05 Lead", A2="Polyvac", "10 P. Vac", A2="Mixing Dirll","19 M Drill", 1=1, "") |
|
|
(A3 - list field) | =IFs(A3="Vacuum","01 Vacuum",A3="Grinder","01 Grinder",A3="Ext Lead 1","04 Lead",A3="Ext Lead 2","05 Lead", A3="Polyvac", "10 P. Vac", A3="Mixing Dirll","19 M Drill", 1=1, "") |
|
|
(A4 - list field) | =IFs(A4="Vacuum","01 Vacuum",A4="Grinder","01 Grinder",A4="Ext Lead 1","04 Lead",A4="Ext Lead 2","05 Lead", A4="Polyvac", "10 P. Vac", A4="Mixing Dirll","19 M Drill", 1=1, "") |
|
|
Calculations can occur across all rows & columns within the prefilled table. So whichever format you choose, you can reference any cell.