IF(ISBLANK formula for multiple columns

Cheryl Collins

Hi

I want a formula to show which team has been selected across multiple columns:

So if Construction has been chosen, in one of the columns, this will show in the Team column. Only one team will ever be selected per row. The formula I have is shown above but it's returning a #impasserable

I'm assuming I've got too many IF(ISBLANK in there but, 🤷🏽‍♀️

=IF(ISBLANK([BPR Teams]@row), [BPR Teams]@row, ""), IF(ISBLANK([BSv Teams]@row), [BSv Teams]@row, ""), IF(ISBLANK([Bus. Ops Teams]@row), [Bus. Ops Teams]@row, ""), IF(ISBLANK([C & M Teams]@row), [C & M Teams]@row, ""), IFISBLANK([Finance Teams]@row, [Finance Teams]@row, ""), IF(ISBLANK([HAL Teams]@row), [HAL Teams]@row, ""), ,IFISBLANK([HR Teams]@row), [HR Teams]@row, ""), IF(ISBLANK([IPR Teams]@row), [IPR Teams]@row, ""), IF(ISBLANK([IT Teams]@row), [IT Teams]@row, ""), IF(ISBLANK([LSS Teams]@row), [LSS Teams]@row, ""), IF(ISBLANK([R & C Teams]@row), [R & C Teams]@row, ""), IF(ISBLANK([RE Teams]@row), [RE Teams]@row, "")

If anyone can help, I would appreciate it.

TIA

Cheryl

Cheryl Collins

I've solved my own problem using a far simpler formula. As I know there will only ever be one team selected, I have used a simple JOIN formula to pull the team name from my teams column range into a single team column:

BullandKhmer

Take out the ""), at the end of every IF statement then add as many ) at the end as ""), you have taken out.

BullandKhmer

=IF(ISBLANK([BPR Teams]@row), [BPR Teams]@row, IF(ISBLANK([BSv Teams]@row), [BSv Teams]@row, IF...............,"")))))))))))

Andrée Starå

Hi @Cheryl Collins

I hope you're well and safe!

Another option instead of a formula could be a Workflow combined with the Change Cell Action.

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Cheryl Collins

Hi @BullandKhmer

Thank you for your response.

So, my formula looks like this:

=IF(ISBLANK([BPR Teams]@row), [BPR Teams]@row, IF(ISBLANK([BSv Teams]@row), [BSv Teams]@row, IF(ISBLANK([Bus. Ops Teams]@row), [Bus. Ops Teams]@row, IF(ISBLANK([C & M Teams]@row), [C & M Teams]@row, IFISBLANK([Finance Teams]@row, [Finance Teams]@row, IF(ISBLANK([HAL Teams]@row), [HAL Teams]@row, ,IFISBLANK([HR Teams]@row), [HR Teams]@row, IF(ISBLANK([IPR Teams]@row), [IPR Teams]@row, IF(ISBLANK([IT Teams]@row), [IT Teams]@row, IF(ISBLANK([LSS Teams]@row), [LSS Teams]@row, IF(ISBLANK([R & C Teams]@row), [R & C Teams]@row, IF(ISBLANK([RE Teams]@row), [RE Teams]@row, "")))))))))))

but, I'm still getting an #unparesable error

TIA

Cheryl

Cheryl Collins

Hi @Andrée Starå

I'm good thanks, I hope you are too?

A workflow is an interesting option. I'm not entirely sure how this would work though as I can't see in the workflow wizard a way of creating this, unless I do a workflow for each team column?

TIA

Cheryl

Andrée Starå

@Cheryl Collins

Excellent!

Glad to hear it, and I am too!

You could add one Workflow with multiple Condition Paths.

Make sense?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Cheryl Collins

Hi @Andrée Starå

Glad to hear you are well.

I've added the conditions but when it asks me to put the cell value do, I just use the column name in {{ }} as below? Because the text will depend on the value held in the respective Teams columns

Thanks

Cheryl

Andrée Starå

@Cheryl Collins

No, you'd need to define each value in the Change cell section for each Condition path.

Make sense?

Cheryl Collins

Hi @Andrée Starå

Sorry, no I don't understand. The Change cell value still wants text adding and it will depend on what the drop down says as there are multiple teams under e.g. BPR team. If someone selects Commercial Dispute Resolution as a BPR Team I want that team to populate the Team column (so, I can eventually hide all the department teams columns).

Hope that makes more sense.

Thanks

Cheryl

Andrée Starå

@Cheryl Collins

No worries!

Maybe I misunderstood.

My thinking is that you'd check to add all the columns for the different options and then use the Change cell in the Team column.

Make sense?

Cheryl Collins

Morning @Andrée Starå

I think what I'm struggling with is what I put in the Change Cell text box as it won't allow me to use the curly brackets that looks up a cell reference. In the Change Cell text it needs to pull through which ever team has been entered in one of the Department Teams columns.

Thanks

Cheryl

Cheryl Collins

I've solved my own problem using a far simpler formula. As I know there will only ever be one team selected, I have used a simple JOIN formula to pull the team name from my teams column range into a single team column: