This guide explains how to automatically populate text or numeric values in cells adjacent to a list (drop-down) field in a Dashpivot Web table. When a value is selected from the drop-down list, the formula will return related information — such as costs, descriptions or codes — ensuring data consistency and reducing manual entry. You use the IFS() function to map list selections to output values.
Prerequisites
Before you begin make sure you have the following:
Plan: Standard, Pro, Premium, or Platinum
Permission: Org Controller (Home Folder) or Org Controller, Project Controller, Team Controller (Team Folder)
Skills Required: Dashpivot, Excel formula knowledge
Device Type: computer
Subscription: Dashpivot Web
Step-by-Step Instructions
Open your Dashpivot Web template and navigate to the table where you want to auto-populate information.
This can be a Default Table or a Prefilled Table.
Configure the column where the user will make a selection as a List field (drop-down).
You can link this to an existing list or manually enter the list options.
Select the cell where you want the related information (text or number) to be auto-populated.
Set this as a Formula field.
Use an
IFS()formula to map drop-down values to the values you want displayed.The general syntax is:
=IFS( Condition1, Value1, Condition2, Value2, Condition3, Value3, …, 1=1, "" )
The final
1=1, ""ensures that if none of the conditions are true, the formula returns a blank.
Example – Default Table
Suppose you have a list of materials and you want the Unit Cost ($) to automatically appear when the material is selected:
Material (List) | Unit Cost ($) (Formula) |
A1 | B1 |
In the display cell B1, enter:
=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, "" )
In the same row you can perform further calculations — e.g., multiply the unit cost by a quantity field:
= B1 * C1
Because this is a Default Table, when the user adds a new row, Dashpivot applies the same formula across all rows.
Example – Prefilled Table
For a Prefilled Table with a fixed number of rows:
In B1 (the first row’s formula cell), use the same
IFS()logic as above.Adjust the row reference for the other rows — for example in B2 and B3:
=IFS(A2="Crushed stone (metric ton) - Base course",15,1=1,"")
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 |
=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,"")
In each case, the formula will display the relevant unit cost based on the list selection.
Notes
Default Tables don’t have a fixed number of rows — adding more rows will apply the same formula logic to new rows.
Prefilled Tables let you set a fixed number of rows, and formulas can reference any cell within or across tables