IF Column Formula with Drop Down options

Tina Rustvold

We are trying to create a formula that will recognize a service line type and auto populate the corresponding division in another column, but only for one unique circumstance. Only our Critical Care service lines also need to be Critical Care region.

Below is the formula I tried, but when I make it a column formula, anything that is not Critical care loses the assigned region. Is there a 'IF false' option that would leave the existing or added data in these cells?

=IF([Service Line]@row = "Critical Care", "Critical Care")

What it should do

What what happens with in a column formula

Carson Penticuff

I am understanding correctly that you wish the column on the right to be entered manually, but any entry of "Critical Care" in the left column will override the right column in that row? If this is what you are looking for, this is not possible with a formula. You can have manual entry or a formula, but not both.

The only way I can envision this working is through automation. You could setup a workflow so that anytime a row is changed or added and "critical care" is in the specific row, the corresponding entry in the other column can be changed as well.

Tina Rustvold

@Carson Penticuff Thank you! You have it exactly right, and I completely forgot about the change text workflow. I get so used to utilizing one thing I forget about the other--appreciate the reminder!

Jaime M.

Hey!

Does "Tele HH" and "Hospital Medicine" etc. also need to populate something specific in the second row? If so, I would recommend using another sheet and doing an index and match function that references it.

First create your reference sheet where you list the possible service line with what should populate in the second column (titled "Populate with"):

Then, in your intake sheet, add a formula that references the new reference sheet:

=INDEX({Test - Populate with}, MATCH([Service Line]@row, {Test - Service Line}, 0))

Hope this helps!

Jaime