Assigning Select and Multiselect Values with Formulas

  • Updated

Many of Spreadsheet.com's unique data types (including the Select and Multiselect data types) can be assigned via formulas. Combining formulas with the Select or Multiselect data types allows you to create complex logic to determine which select values to assign, possibly referencing other cells, or even have your worksheets change select options based on changes to data in other worksheets.

How to input formulas in Select and Multiselect cells

Like other cells, you can input a formula into Select and Multiselect data type cells by typing in the formula. Simply select the select cell, click on the function bar, and type in the desired formula.

mceclip0.png

Note: Select data types can either reference an existing Select cell as an output to return a select data type, or use the select option as text (for example, "Late" to match mceclip0.png). However Multiselect data type cells can only use formulas that use cell references for their outputs.

For Example

For example, in our team's task tracker, we want to determine if tasks are on track, likely to be delayed, or late. To do this, we create a formula that compares today's date to the planned end date, and check the status to make sure the task isn't yet "Completed". In the animation below, we go into a select data type cell, add in our formula, and then assign a select value based on the output of the formula.

Assigning_Select_and_Multiselect_by_formula.gif

The formula we paste in looks for whether the task is "On Track", "Delayed", or "Late" and assigns the accompanying select option. You can specify the output both as text that matches the select option (for instance "Late" to match mceclip0.png) or reference a cell with a select option in it (for instance G4, if G4 had mceclip1.png in it).

 

To see other data types that can use formulas to assign values, check out our advanced formatting articles. Or see our articles on the Select and Multiselect data types, for more on those data types.