If function based on TBD cells w/3 options

Derek Williams
edited 07/22/21 in Formulas and Functions

I'm trying to create an email subject line helper cell for my projects. Different project numbers are added throughout my project's life. I start with a VRE#, then later get a Site#, then lastly get a Store#.

Those numbers are always added in that order.

Can you help me write a function for:

If site# and store# are TBD use function in line 10

If site# is TBD use function in line 9

If all 3 are present, use function in row 8

I added a ' in the first column for you to see the functions for each. Hope this makes sense!


Best Answer

  • Derek Williams
    Answer ✓

    Got it!

    =IF([Site #]@row = "TBD", [Column6]@row + [VRE #]@row + [Column7]@row + " " + City@row + " " + State@row + " " + [Column6]@row + Address@row + [Column7]@row + " " + [Column8]@row, (IF([Store #]@row = "TBD", [Site #]@row + " " + City@row + " " + State@row + " " + [Column6]@row + Address@row + [Column7]@row + " " + [Column8]@row, [Site #]@row + [Column8]@row + [Store #]@row + " " + City@row + " " + State@row + " " + [Column6]@row + Address@row + [Column7]@row + " " + [Column8]@row)))

Answers

  • This didn't work...

    =IF([Store #]@row = "TBD", [Site #]@row + " " + City@row + " " + State@row + " " + [Column6]@row + Address@row + [Column7]@row + " " + [Column8]@row), IF[Site#]@row = "TBD", [VRE #]@row + [Column8]@row + [Store #]@row + " " + City@row + " " + State@row + " " + [Column6]@row + Address@row + [Column7]@row + " " + [Column8]@row, [Site #]@row + [Column8]@row + [Store #]@row + " " + City@row + " " + State@row + " " + [Column6]@row + Address@row + [Column7]@row + " " + [Column8]@row)

  • I think this will get me there. Tying to figure this out now...


    Smartsheet IF(OR Learning Center Example....

    IF(OR([Due Date]1 > [Due Date]2, [Due Date]1 > [Due Date]3), "Due Date 1 isn't the smallest", "Due Date 1 is the smallest")

    Syntax

    OR( logical_expression1 [ logical_expression2​... ])

    • logical_expression1 — An expression that evaluates to either true or false (boolean).
    • logical_expression2​... —[optional] Additional expressions that evaluate to true or false.


  • IF(OR didn't work.

    Maybe I'm looking for:

    =IF(logical_expression1, true, (logical_expression2, true), false)

    Anyone?

  • Derek Williams
    Answer ✓

    Got it!

    =IF([Site #]@row = "TBD", [Column6]@row + [VRE #]@row + [Column7]@row + " " + City@row + " " + State@row + " " + [Column6]@row + Address@row + [Column7]@row + " " + [Column8]@row, (IF([Store #]@row = "TBD", [Site #]@row + " " + City@row + " " + State@row + " " + [Column6]@row + Address@row + [Column7]@row + " " + [Column8]@row, [Site #]@row + [Column8]@row + [Store #]@row + " " + City@row + " " + State@row + " " + [Column6]@row + Address@row + [Column7]@row + " " + [Column8]@row)))

  • Hi @Derek Williams

    I'm glad you figured it out! Thanks for sharing your solution and process.

    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!