Can COLLECT Criteria use text values?
I am trying to find the earliest date in a range of dates. But I only want to consider dates that have a certain text value in a corresponding cell.
Here is the formula I'm trying to get to work:
=MIN(COLLECT([Prod Date 1 (copy)]@row:[Prod Date 8 (copy)]@row, [Prod Date 1 - (ON / OFF / DARK / LIVE) (copy)]@row:[Prod Date 8 - (ON / OFF / DARK / LIVE) (copy)]@row), ="ON/LIVE")
[Prod Date [n] (copy)] range are dates
[Prod Date [n] - (ON / OFF / DARK / LIVE) (copy)] range are text
I get an "#INCORRECT ARGUMENT SET" error with this formula.
Would appreciate any help on what I'm doing wrong here.
Thanks!
Answers
-
You have it, but you are using two @row ranges referencing different rows. If you your formula is in the row of dates and the row containing your "ON/LIVE" values is row 4, your formula would be:
=MIN(COLLECT([Prod Date 1 (copy)]@row:[Prod Date 8 (copy)]@row, [Prod Date 1 - (ON / OFF / DARK / LIVE) (copy)]4:[Prod Date 8 - (ON / OFF / DARK / LIVE) (copy)]4), ="ON/LIVE")
-
Hi Randy -
Thanks for your answer. However, ALL of the data lives on a single row. This is a sheet of project status and each row represents a Project. Each project row has 8 possible deployment dates and each deployment date has a "type" field. So I am trying to find the earliest deployment date with the "ON/LIVE" deployment "type".
Hopefully that makes sense. Perhaps it's because everything is on the same row that the formula is puking on me?
Thanks,
Tony
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!