Chaining "If" Functions

Options

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!

Tags:

Answers

  • brianschmidt
    brianschmidt ✭✭✭✭✭
    Options

    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
    Options

    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
    Options

    @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
    brianschmidt ✭✭✭✭✭
    Options

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!