Select Current Data Submitted per Project in ONE collector sheet
One of our support services has created a single collector sheet to collect responses via a form across multiple projects. When project information changes, responses are resubmitted for that project, adding another line item for that project in the collector sheet. We need to find a solution that will collect the most current row for each project listed on this one collector sheet, and either 1) input the current data for each project in a summary field so we can pull the data from the sheet summary or 2) create a collector report of current data only to create a metric widget on a dashboard.
We have created a check box helper column, =IF(Submitted@row = MAX(Submitted:Submitted), 1)
But it (obviously) will only find the most current line, not the most current line of each project. I need it to do that for EACH project listed on the sheet. Is this possible with some version of a Max/Collect syntax?
Best Answers
-
Try this:
=IF(Submitted@row = MAX(COLLECT(Submitted:Submitted, [Project Name]:[Project Name])), 1)
-
Hello @MA Design
You're missing the criterion [Project Name]@row
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined
Answers
-
Try this:
=IF(Submitted@row = MAX(COLLECT(Submitted:Submitted, [Project Name]:[Project Name])), 1)
-
Thanks @Paul Newcome - that gives me an "incorrect argument set" error. I believe I am missing the 'Criterion1' in that syntax:
Range: Submitted:Submitted
Criterion Range_1: [Project Name]:[Project Name]
Criterion1: ?
-
Hello @MA Design
You're missing the criterion [Project Name]@row
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined -
Always the simple things! That worked thank you!
-
Sorry about that. I was in a hurry and got ahead of myself.
=IF(Submitted@row = MAX(COLLECT(Submitted:Submitted, [Project Name]:[Project Name], @cell = [Project Name]@row)), 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!