Iferror challenge

Options

Hi, I'm trying to set up a formula to return the following information:

If the "Pad Possession" (a date) and the "Fixturing Period" (a number) both exist, then add the number of days to the date and return the new date.

If either or both of the Pad Possession or Fixturing Period cells are blank, then return "Awaiting Possession".

I used the "IFERROR" formula (pasted below), which works if either the Pad Possession or Fixturing Period cells are blank, but if both are blank then there is no error, and the formula returns nothing.

=IFERROR([PAD Possession Done / A&W Fixturing Started]@row + [Fixturing Period (Days)]@row, "Awaiting Possession")

Can anyone suggest how I can make this work? Thank you!

Answers

  • AaronO
    AaronO ✭✭✭
    Options

    Strange - I see a different behavior:

    Using =IFERROR(Pad@row + Period@row, "Awaiting Possession") I get the error if they're both blank; if there's a [Period] but no [Pad] the result is a blank.

    In any case, would this work? It's a little brute-force-y but maybe more predicable...

    =IF(AND(NOT(ISBLANK(Pad@row)), NOT(ISBLANK(Period@row))), Pad@row + Period@row, "Awaiting Posession")

    Gives me:

    Aaron

  • A&W Admin
    A&W Admin ✭✭✭
    Options

    Thanks Aaron. I see in principle how your formula will work. I copied and paste the formula into my sheet, changing the references. However, I'm getting a circular reference error. Any suggestions?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @A&W Admin

    Can you clarify what column you're pasting the formula in to? Would you be able to post a screen capture of your sheet (but please block out sensitive data)?

    Cheers,

    Genevieve

  • A&W Admin
    A&W Admin ✭✭✭
    Options

    This is my formula


    =IF(AND(NOT(ISBLANK([PAD Possession Done / A&W Fixturing Started]@row)), NOT(ISBLANK([Fixturing Expiry]@row))), [Fixturing Expiry]@row + [PAD Possession Done / A&W Fixturing Started]@row, "Awaiting Possession")




  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @A&W Admin

    Thank you for the image! I can see that you're pasting the formula into the "Fixturing Expiry" column, but you're referencing this column in the formula, which is why you're getting an error.

    =IF(AND(NOT(ISBLANK([PAD Possession Done / A&W Fixturing Started]@row)), NOT(ISBLANK([Fixturing Expiry]@row))), [Fixturing Expiry]@row + [PAD Possession Done / A&W Fixturing Started]@row, "Awaiting Possession")

    I believe that instead of using [Fixturing Expiry] you were intending to use the [Fixturing Period (days)] column. Here's the same formula @AaronO suggested above, using your column names:

    =IF(AND(NOT(ISBLANK([PAD Possession Done / A&W Fixturing Started]@row)), NOT(ISBLANK([Fixturing Period (Days)]@row))), [PAD Possession Done / A&W Fixturing Started]@row + [Fixturing Period (Days)]@row, "Awaiting Posession")


    Let me know if that makes sense and works for you!

    Cheers,

    Genevieve

  • A&W Admin
    A&W Admin ✭✭✭
    Options

    Thank you so much! Part of the challenge for me was getting the column to reference correctly in the formula (our sheet has way too many columns so I was trying to scroll back and forth). I ended up moving the formula column right beside the columns I am referencing, and then referencing the columns was a breeze! I appreciate your patience and help. Thank you again.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Glad to hear you sorted it out! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!