Iferror challenge

A&W Admin

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!

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

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.

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

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.

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

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.

Glad to hear you sorted it out! 🙂