1

I am trying to create a formula that will auto-populate a column based on the input from two other columns. The auto population would need to check the boxes of the appropriate items that apply based on the input.

Example:

Column A - Flight Type:

  • Orientation
  • Spouse
  • Private

Column B - Aircraft type:

  • Airbus 100
  • Airbus 200
  • Airbus 300

Depending on the selection of Column A and B I would like C to auto-populate required tasks from 10 choices.

1 Answer 1

0

You can create a calculated column with conditions based on two columns like:

=IF(AND([Flight Type] = "Orientation", [Aircraft type] = "Airbus 100"), "Task 1", IF(AND([Flight Type] = "Orientation", [Aircraft type] = "Airbus 200"), "Task 2", IF(AND([Flight Type] = "Orientation", [Aircraft type] = "Airbus 300"), "Task 3", "")))

Similarly, you can add conditions for other choices of Flight Type & Aircraft type

Final formula should be something like:

=IF(AND([Flight Type] = "Orientation", [Aircraft type] = "Airbus 100"), "Task 1", IF(AND([Flight Type] = "Orientation", [Aircraft type] = "Airbus 200"), "Task 2", IF(AND([Flight Type] = "Orientation", [Aircraft type] = "Airbus 300"), "Task 3", IF(AND([Flight Type] = "Spouse", [Aircraft type] = "Airbus 100"), "Task 4", IF(AND([Flight Type] = "Spouse", [Aircraft type] = "Airbus 200"), "Task 5", IF(AND([Flight Type] = "Spouse", [Aircraft type] = "Airbus 300"), "Task 6", IF(AND([Flight Type] = "Private", [Aircraft type] = "Airbus 100"), "Task 7", IF(AND([Flight Type] = "Private", [Aircraft type] = "Airbus 200"), "Task 8", IF(AND([Flight Type] = "Private", [Aircraft type] = "Airbus 300"), "Task 9", "Default Task")))))))))

Note:

  1. Sometimes comma(,) does not work in formula (I am not sure but it is based on something language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).

  2. Set The data type returned from this formula is: to Single line of text in column settings.

Documentation: Calculated Field Formulas

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.