Chaining "If" Functions

bletou

Hello,

I'm attempting to capture if (4) columns contain DB01-DB12, and if a cell contains DB1-DB12 then it returns ONLY DB01-DB12 (see below for reference). I managed a formula for Db01 and DB02 but once I try DB03 I get an error (likely because it registers as IF False). Is there a better way to string multiple IF(CONTAINS() functions? Or even better, is there a better way to capture the cell instead of writing an IF(Contains function for 12 different entries?

See below

Any help would be appreciated!

brianschmidt

You are correct in your assumption with regards to the if statement only checking statements if the prior statements are false. I would try to following formula, ensuring that your "Reference column hidden" column is also set to be a multi-select dropdown column:

=JOIN(COLLECT([Affected Switchgear & Switchboards]@row:[Affected Branch Panels]@row, [Affected Switchgear & Switchboards]@row:[Affected Branch Panels]@row, CONTAINS("DB", @cell)), CHAR(10))

Hope this helps!

bletou

Thank you @brianschmidt! This is closer to what I'm looking for, however, I'm hoping to only return the "DB" value similar to my initial post where DB01 and DB02 are examples of the returned value.

The purpose is to create a report grouping rows by "DB01", "DB02", etc through "DB12" without the added fluff of the EDS Prefix and the suffix.

So the return value should look like this

I guess I could make multiple columns using IF(CONTAINS( then use a collect function to return the DB01, DB02, DB03 etc into a single column for reporting but it seems there should be an easier way

bletou

@brianschmidt- I used your formula with some hidden reference columns and was able to get exactly what I was looking for! Appreciate your help!

brianschmidt

https://community.smartsheet.com/discussion/comment/423069#Comment_423069

Ahh, yes. It looks like in the test sheet I was using, I didn't use a full string of characters like you have here. I'm glad you were able to make it work!