Getting value of cell in another sheet based on multiple criteria using OR

Karen Harker

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

Genevieve P.

Hi @Karen Harker

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

Genevieve P.

Hi @Karen Harker

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

Karen Harker

Yes, that worked! I will investigate the @cell and @row further.

Thank you very much.

Karen Harker

Genevieve P.

No problem! I'm glad I could help.