get list of projects worked within last month (without helper fields)

edited 12/09/19 in Formulas and Functions

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.


  • Jacob
    edited 09/26/19

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!