Populating information/texts/numbers based on drop-down selection means that when a value is selected from the drop-down list, other cells are automatically populated with its relative information.
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 column as your 'List' Field.
To populate information, use the formula IFS():
=IFs(Condition1, Value1, Condition2, Value2, Condition3, Value3, Condition_n, Value_n)
For example:
Given a dataset of building materials and their unit cost:
Building materials | Unit cost ($) |
Crushed stone (metric ton) - Base course | 15 |
Sand (metric ton) - Concrete | 10 |
Sand (metric ton) - Masonry | 12 |
Asphalt paving (metric ton) - PG 58 | 400 |
Masonry cement (bag) - 70-lb | 10 |
You want that in your form, upon selection of building materials, their prices are automatically populated in the display cells.
Note: If given a data list of 100 items, for a default table, you can continue adding more rows but that doesn't mean you're going to get all 100 items in the list. For a prefilled table, you can set the maximum number of rows in a table.
For default table:
Suppose you have a default table and there is no limit in the number of rows to be added. Upon the selection of a material (list field), the display cell (B1) automatically displays the set unit cost for that material (based on the given data set).
Use the IFS() formula in the display cell B1:
=IFS(A1="Crushed stone (metric ton) - Base course", 15, A1="Sand (metric ton) - Concrete", 10, A1="Sand (metric ton) - Masonry", 12, A1="Asphalt paving (metric ton) - PG 58", 400, A1="Masonry cement (bag) - 70-lb", 10, 1=1, "")
There should be one true value in the conditions, therefore use '1=1, ""'.
Material | Unit Cost ($) | Quantity | Total Cost ($) |
(A1 - list field) | =IFS(A1="Crushed stone (metric ton) - Base course", 15, A1="Sand (metric ton) - Concrete", 10, A1="Sand (metric ton) - Masonry", 12, A1="Asphalt paving (metric ton) - PG 58", 400, A1="Masonry cement (bag) - 70-lb", 10, 1=1, "") | (C1 - number field) | = B1*C1 |
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 there is a fixed number (3 only) of items to be purchased in one form therefore you use a prefilled table. Upon the selection of a material (list field), the display cells (B1, B2, and B3) automatically display the set unit cost for that material (based on the given data set).
Use the IFS() formula in the display cell B1, and adjust accordingly to the other B cells:
=IFS(A1="Crushed stone (metric ton) - Base course", 15, A1="Sand (metric ton) - Concrete", 10, A1="Sand (metric ton) - Masonry", 12, A1="Asphalt paving (metric ton) - PG 58", 400, A1="Masonry cement (bag) - 70-lb", 10, 1=1, "")
There should be one true value in the conditions, therefore use '1=1, ""'.
Material | Unit Cost ($) | Quantity | Total Cost ($) |
(A1 - list field) | =IFS(A1="Crushed stone (metric ton) - Base course", 15, A1="Sand (metric ton) - Concrete", 10, A1="Sand (metric ton) - Masonry", 12, A1="Asphalt paving (metric ton) - PG 58", 400, A1="Masonry cement (bag) - 70-lb", 10, 1=1, "") | (C1 - number field) | = B1*C1 |
(A2 - list field) | =IFS(A2="Crushed stone (metric ton) - Base course", 15, A2="Sand (metric ton) - Concrete", 10, A2="Sand (metric ton) - Masonry", 12, A2="Asphalt paving (metric ton) - PG 58", 400, A2="Masonry cement (bag) - 70-lb", 10, 1=1, "") | (C2 - number field) | = B2*C2 |
(A3 - list field) | =IFS(A3="Crushed stone (metric ton) - Base course", 15, A3="Sand (metric ton) - Concrete", 10, A3="Sand (metric ton) - Masonry", 12, A3="Asphalt paving (metric ton) - PG 58", 400, A3="Masonry cement (bag) - 70-lb", 10, 1=1, "") | (C3 - number field) | = B3*C3 |
Calculations can occur across all rows & columns within the prefilled table. So whichever format you choose, you can reference any cell.