Iferror challenge

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 ✭✭✭

    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 ✭✭✭

    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?

  • 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

    Need more help? 👀 | Help and Learning Center

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

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

    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")




  • 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

    Need more help? 👀 | Help and Learning Center

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

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

    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.

  • Glad to hear you sorted it out! 🙂

    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!