To populate information/text/numbers in adjacent cells with Yes/No selections, use either a Default or Pre-filled table, whichever suits your form. Either way, the formula stays the same.
To start, navigate your cursor to the left tab and drag the table you wish to use. You can set which column is intended for the Yes/No field. Set the Yes/No cell as a list and manually type 'Yes' and 'No', or for Yes/No/NA field include 'NA'. Set the display cell you wish to populate the information as a 'Formula field'.
To populate information/text/numbers, use the formula:
=IFS(Condition1, Value1, Condition2, Value2, Condition3, Value3, Condition_n, Value_n)
Examples:
In a default table
Suppose you are creating a hazard assesment form and you have a register for the potential hazards. A column is dedicated whether this hazard has been addressed or not.
If "Yes" is selected, the text "Hazard has been addressed. Sign in the next column." is prompted.
If "No" is selected, the text "Hazard not yet addressed." is prompted.
Use the formula in the display cells in cell C1
=IFS(B1="Yes", "Hazard has been addressed. Sign in the next column.", B1="No", "Hazard not yet addressed.", 1=1, "")
There should be one true value in the conditions, therefore use '1=1, ""'.
Hazard | Has hazard been addressed? (Yes | No) | Display | Signature |
| (B1 - list field) | =IFS(B1="Yes", "Hazard has been addressed. Sign in the next column.", B1="No", "Hazard not yet addressed.", 1=1, "") | (D1 - signature field) |
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.
In a prefilled table
Suppose you are creating a checklist and upon the selection of Yes/No/N/A in column B, a text prompt shows up at column C.
When 'Yes' is selected, the text 'PASSED' is populated
When 'No' is selected, the text 'FAILED' is populated
When 'N/A' is selected, the text 'SKIP' is populated
Use the formula in the display cells in Column C and adjust accordingly,
=IFS(B1="Yes", "PASSED", B1="No", "FAILED", B1="N/A", "SKIP", 1=1, "")
There should be one true condition in the formula, that's why '1=1. ""' is added.
Checklist Questions | Yes/No/NA | Display |
A1 | (B1 - list field) | =IFS(B1="Yes", "PASSED", B1="No", "FAILED", B1="N/A", "SKIP", 1=1, "") |
A2 | (B2 - list field) | =IFS(B2="Yes", "PASSED", B2="No", "FAILED", B2="N/A", "SKIP", 1=1, "") |
A3 | (B3 - list field) | =IFS(B3="Yes", "PASSED", B3="No", "FAILED", B3="N/A", "SKIP", 1=1, "") |
A4 | (B4 - list field) | =IFS(B4="Yes", "PASSED", B4="No", "FAILED", B4="N/A", "SKIP", 1=1, "") |
A5 | (B5 - list field) | =IFS(B5="Yes", "PASSED", B5="No", "FAILED", B5="N/A", "SKIP", 1=1, "") |
Calculations can occur across all rows & columns within the prefilled table. So whichever format you choose, you can reference any cell.