Formula based on multiple fields
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.
Kristie Diersen 😀
Best Answer
-
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!
Answers
-
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!
-
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.
Kristie Diersen 😀
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!