Formula based on multiple fields

Kristie Diersen

I am looking to write a formula that will return the the oldest date in the "Date Sent" column if the "Date Approved" column is blank AND the "Pending" column is yellow. I have figured out how to return the oldest date in the "Date Sent" column if the "Date Approved" column is blank:

=MIN(COLLECT({CS Article Tracker Range 3}, {CS Article Tracker Range 4}, @cell = ""))

Where - Range 3 is Date Sent and Range 4 is Date Approved

but can't figure out how to add in the Pending column as a criteria.

David Joyeuse

Hi @Kristie Diersen

That should be this:

=MIN(COLLECT({CS Article Tracker Range 3}, {CS Article Tracker Range 4}, "", {CS Article Tracker - Pending Range}, "Yellow"))

Also I would suggest to use more defined names when using crossheet ranges so your formula is easier to understand in the near to distant future. As a rule I norm them this way: Sheet - Column - Cells if not full column.

Hope it helped!

David Joyeuse

Hi @Kristie Diersen

That should be this:

=MIN(COLLECT({CS Article Tracker Range 3}, {CS Article Tracker Range 4}, "", {CS Article Tracker - Pending Range}, "Yellow"))

Also I would suggest to use more defined names when using crossheet ranges so your formula is easier to understand in the near to distant future. As a rule I norm them this way: Sheet - Column - Cells if not full column.

Hope it helped!

Kristie Diersen

Hi @David Joyeuse

I usually name my crossheet ranges, I was just trying to figure it out so I hadn't named them yet.

This worked perfectly. Thank you for the assistance.