Getting value of cell in another sheet based on multiple criteria using OR
I have a sheet (Data Updates) that is a log of updates to all of our databases, including future updates. The log includes the Product, the Updated Through Date, a Start Date [to start the update], an End Date [update should be completed by this date], and a Status (options: Next Up [this is the Updated Through date that is next to be updated], In Progress, Completed, and others that are not relevant to my question).
I also have a Data Updates Summary sheet with one Product per line showing details about the product, including the last update made (Status=Completed).
I want to see on the Data Updates Summary sheet the {Data Updates Updated Through Date} which meet these conditions: [Data Updates Summary Product]={Date Updates Product} AND ({Data Updates Status}="Next Up" or "In Progress").
I can use MIN(COLLECT)) to get the last one that was completed, but it's the "OR" part of my requirement that is problematic. How can I do this?
Thanks!
Karen Harker
Best Answer
-
In your COLLECT function you can list the {Range} you're searching through for the Status, then use the OR function as your criteria to list both options. You'll want to use @cell in the OR Function like so:
OR(@cell = "In Progress", @cell = "Next Up")
See Create Efficient Formulas with @cell and @row
Try a structure like this:
=MIN(COLLECT({Date Column}, {Product Column}, Product@row, {Status Column}, OR(@cell = "In Progress", @cell = "Next Up")))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
In your COLLECT function you can list the {Range} you're searching through for the Status, then use the OR function as your criteria to list both options. You'll want to use @cell in the OR Function like so:
OR(@cell = "In Progress", @cell = "Next Up")
See Create Efficient Formulas with @cell and @row
Try a structure like this:
=MIN(COLLECT({Date Column}, {Product Column}, Product@row, {Status Column}, OR(@cell = "In Progress", @cell = "Next Up")))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Yes, that worked! I will investigate the @cell and @row further.
Thank you very much.
Karen Harker
-
No problem! I'm glad I could help.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!