get list of projects worked within last month (without helper fields)
I have a task list that includes what project the task belongs to. The project field is filled in manually (not a preexisting list).
I would like to setup a dashboard to show a distinct list of projects that had been worked on, how much time was spent on them, etc.
Where I am having trouble is how do I get a distinct list of project names from the task sheet to put into my metric sheet;
We have external processes that work on the task sheet, so adding an extra helper fields that 'flags as duplicate' and then 'pull all non-duplicate' can cause issues; is there a way to do this from outside the task list? (without adding helper fields in the task list)
I know how to do formulas, so if there is a function that would help- I'd greatly appreciate being pointed in the right direction. I did do a search, but the last forum post on this was from 2017 and indicated that feature would be added in the future, and then all the other post seem to indicate putting in a helper formula on the base data sheet.
Comments
-
ok, for anyone else who has this issue the answer is
=IFERROR(INDEX(DISTINCT(COLLECT({Project Range}, {Closed Date}, @cell >= {DateStartLastWeek})), 1), "")
where 1 is the entry number,
Collect=get the data and apply filters
Distinct=just unique values
Index=which row in the list
IfError=when there are no results to prevent all connecting formulas from showing errors
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!