Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
If Contains lookup to another sheet result is empty show value from other cell in row?
I have the following formula in a cell that until now was successfully looking up a date in another sheet and when the match of "OSHA Inspections" was true in that other sheet it would grab the most recent date for the shipto id. What I'm trying to do is modify the formula so that when the date field is blank in the lookup, it instead shows a date that is in a different column of the sheet where the formula is located. The below is still successfully looking up the date from the second sheet but, the cells are remaining blank when the lookup doesn't return a date. Any tips on how to get this working would be appreciated.
=IF(CONTAINS("OSHA Inspections", {SVC Orders Range 8}), MAX(COLLECT({SVC Orders ORDER_DATE}, {SVC Orders SHIPTO}, [SHIPTO_ID]@row)), [Last Inspection Date]@row)
Best Answer
-
This seems to be working.
=IF(ISBLANK(CONTAINS("OSHA Inspections", {SVC Orders Range 8})), (MAX(COLLECT({SVC Orders ORDER_DATE}, {SVC Orders SHIPTO}, [SHIPTO_ID]@row))), [Last Inspection Date]@row)
Answers
-
In English, your logic would just be that If (your original formula) = blank, then do another formula, otherwise, (your original formula). In Smartsheet, it'd look like below, just replace the «INSERT OTHER FORMULA HERE» with the lookup to that second date column.
=IF(IF(CONTAINS("OSHA Inspections", {SVC Orders Range 8}), MAX(COLLECT({SVC Orders ORDER_DATE}, {SVC Orders SHIPTO}, [SHIPTO_ID]@row)), [Last Inspection Date]@row)="", <<INSERT OTHER FORMULA HERE>>, IF(CONTAINS("OSHA Inspections", {SVC Orders Range 8}), MAX(COLLECT({SVC Orders ORDER_DATE}, {SVC Orders SHIPTO}, [SHIPTO_ID]@row)), [Last Inspection Date]@row)
-
This seems to be working.
=IF(ISBLANK(CONTAINS("OSHA Inspections", {SVC Orders Range 8})), (MAX(COLLECT({SVC Orders ORDER_DATE}, {SVC Orders SHIPTO}, [SHIPTO_ID]@row))), [Last Inspection Date]@row)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!