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
-
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
-
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
-
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
-
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! 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!