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.

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, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.