If function based on TBD cells w/3 options

Derek Williams
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!

Screen Capture.JPG


· Share on FacebookShare on Twitter

Best Answer

  • Derek Williams
    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)))

    · Share on FacebookShare on Twitter

Answers

  • Derek Williams
    Derek Williams ✭✭

    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)

    · Share on FacebookShare on Twitter
  • Derek Williams
    Derek Williams ✭✭

    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.


    · Share on FacebookShare on Twitter
  • Derek Williams
    Derek Williams ✭✭

    IF(OR didn't work.

    Maybe I'm looking for:

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

    Anyone?

    · Share on FacebookShare on Twitter
  • Derek Williams
    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)))

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Derek Williams

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

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!