Collect % (column 1) based on a checkbox (column 2) and region (column 3)
What formula will collect a % (column 1) based on a checkbox (column 2 that indicates most recent month/day; column next to it has the specific months/days) and specific region - for example, "region 1" (column 3)
Best Answer
-
INDEX, but you wouldn't use the {Range} inside of the CONTAINS function. You would just use @cell exactly as I have it.
Answers
-
I was thinking this would something along the lines of -
=COLLECT({%}, {Max_Date}, 1, {Location}, "Region 1")
I didn't think Smartsheet allows for COLLECT + IF formulas so open to suggestions.
-
I've tried the below as well and getting "Incorrect Argument Set".
=INDEX(COLLECT({%}, {Max_Date}, 1, {Location}, "Region 1"), 1)
-
raw data sheet for reference
-
What are you using to populate your checkbox?
-
@Paul Newcome using =IFERROR(Date@row = MAX(Date:Date), 0)
-
How is the Location column populated? Do the cells have more than just "Region 1" in them?
-
@Paul Newcome yeah there are different regional (and division) names
-
@Paul Newcome the Date and Location columns involve manual intervention
-
Ok. So for the incorrect argument set issue, start by double and triple checking the ranges being referenced. Each should only be a single column.
After that, if there are cells with variations of "Region 1" such as "ABC - Region 1" and "DEF - Region 1", you are going to need to incorporate a CONTAINS function.
CONTAINS("Region 1", @cell)
-
@Paul Newcome copy that! Would this need to an INDEX & COLLECT or just COLLECT?
- =COLLECT({%}, {Location}, CONTAINS("Region 1", {Location}), {Max_Date}, 1)
Or
- =INDEX(COLLECT({%}, {Location}, CONTAINS("Region 1", {Location}), {Max_Date}, 1), 1)
-
INDEX, but you wouldn't use the {Range} inside of the CONTAINS function. You would just use @cell exactly as I have it.
-
@Paul Newcome Totally overlooked the @cell callout but made the update and it works. You're a Smartsheet Community hero - thanks Paul!!
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 438 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 509 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- Hi! I cannot figure this out. I have a "% Complete" column (0, 25, 50, 75, 90, 100, Cancelled). I made a simple checkbox column that should be checked if the item's "% Complete" value is equal to anyt…
- This formula keeps coming up as #UNPARSEABLE. I have checked the name of the columns, they are correct. Any ideas? I am trying to show the days remaining in a project. =IF([% Complete]@row = 1, "", NE…
- Hi All! I´m trying to find the error in this formula but have not been able to find the solution after many attempts. Any ideas? =IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF(…
