This guide explains how to configure logic in Dashpivot Web templates so that selecting an item from a list or drop-down automatically displays corresponding text in another cell. This is useful for conditional displays, automated responses, and structured data outputs within tables. Using the IFs() function and optional AND() logic, you can map list selections to specific text results inside Default or Prefilled Tables.
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 show text based on drop-down selections.
Set one or more columns as a List field.
A List field functions as a drop-down. You can link it to an existing list or manually enter the options.
Plan your logic mapping.
Create a reference of each list value and the text you want displayed for that value.
Select the cell where you want the result text to appear.
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.
Enter your
IFs()formula to map selections to outputs.Example structure:
=IFs(Condition1,Value1,Condition2,Value2,Condition3,Value3)
To populate information from multiple drop-down selections, nest both IFS() and AND() formulas:
Example structure:
=IFs(AND(Condition1, Condition2), Value1, AND(Condition3, Condition4),Value2,AND(Condition5,Condition6),Value3, AND(Condition_x, Condition_y, Value_n)
Test your logic by selecting different values from the List field to confirm the correct text appears in the result cell.
Example: Using Default Table
Automate the Risk Matrix Key in a Default Table within Dashpivot Web by using an IFS() formula. By selecting a Likelihood value in the first column and a Consequence value in the second column, the Key will automatically display the corresponding risk level. This ensures consistent risk classification and removes manual calculations.
Guide Steps:
Open your template and navigate to the Default Table containing the Risk Matrix.
Set the first column as a Likelihood (List) field.
Set the second column as a Consequence (List) field.
Set the third column as a Key (Formula) field.
Before entering the formula, create a guide listing:
Likelihood
Consequence
Key
In the display cell (C1), enter the following formula:
=IFS(
AND(A1="Almost Certain – A",B1="Insignificant – 1"),"H - High",
AND(A1="Almost Certain – A",B1="Minor – 2"),"H - High",
AND(A1="Almost Certain – A",B1="Moderate – 3"),"E - Extreme",
AND(A1="Almost Certain – A",B1="Major – 4"),"E - Extreme",
AND(A1="Almost Certain – A",B1="Catastrophic – 5"),"E - Extreme",
AND(A1="Likely – B",B1="Insignificant – 1"),"M - Medium",
AND(A1="Likely – B",B1="Minor – 2"),"H - High",
AND(A1="Likely – B",B1="Moderate – 3"),"H - High",
AND(A1="Likely – B",B1="Major – 4"),"E - Extreme",
AND(A1="Likely – B",B1="Catastrophic – 5"),"E - Extreme",
AND(A1="Possible – C",B1="Insignificant – 1"),"L - Low",
AND(A1="Possible – C",B1="Minor – 2"),"M - Medium",
AND(A1="Possible – C",B1="Moderate – 3"),"H - High",
AND(A1="Possible – C",B1="Major – 4"),"E - Extreme",
AND(A1="Possible – C",B1="Catastrophic – 5"),"E - Extreme",
AND(A1="Unlikely – D",B1="Insignificant – 1"),"L - Low",
AND(A1="Unlikely – D",B1="Minor – 2"),"L - Low",
AND(A1="Unlikely – D",B1="Moderate – 3"),"M - Medium",
AND(A1="Unlikely – D",B1="Major – 4"),"H - High",
AND(A1="Unlikely – D",B1="Catastrophic – 5"),"E - Extreme",
AND(A1="Rare – E",B1="Insignificant – 1"),"L - Low",
AND(A1="Rare – E",B1="Minor – 2"),"L - Low",
AND(A1="Rare – E",B1="Moderate – 3"),"M - Medium",
AND(A1="Rare – E",B1="Major – 4"),"H - High",
AND(A1="Rare – E",B1="Catastrophic – 5"),"H - High",
1=1,""
)
Note: There should be one true value in the conditions, therefore use 1=1 .
Example: Using a Prefilled Table
Automatically display an equipment Asset Number when a user selects an item from an Equipment list field in a Prefilled Table in Dashpivot Web. This is useful when there is a limit on the number of equipment inspected per day and you want asset numbers to populate automatically without manual entry. The setup uses an IFS() formula to match the selected equipment to its corresponding asset number.
Guide Steps:
Open your template and navigate to the Prefilled Table.
Set Column A as an Equipment (List) field.
Set Column B as an Asset No. (Formula) field.
Use the following equipment data as your reference list:
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 Drill","19 M Drill",
1=1,""
)
Repeat the formula for additional rows, updating the row reference accordingly.
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 Drill","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 Drill","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 Drill","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 Drill","19 M Drill",1=1,"") |
|
|
There should be one true condition in the formula. The final condition
1=1,""ensures the formula returns a blank value if no match is found.Calculations can occur across all rows and columns within the Prefilled Table. You can reference any cell as needed depending on your chosen layout.