IF(ISBLANK formula for multiple columns
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
Best Answer

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:
Answers

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

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

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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

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

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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

No, you'd need to define each value in the Change cell section for each Condition path.
Make sense?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

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?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

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:
Help Article Resources
Categories
Check out the Formula Handbook template!