Collect formulae

Gaurav Chauhan
Gaurav Chauhan ✭✭✭✭✭✭
edited 06/19/23 in Formulas and Functions

Project Plan:

Summary for Graph where formuale needs to be written:

Hi Team, Need your help with writing a formulae on the Summary for Graph sheet wherein the Task bars of the column graph can turn to R/Y/G based on what their status is on the Project plan.

The current Graph populated based on the summary for graph sheet is static but needs to be dynamic to auto pick the color and the % progress.

Basically if we can write like a COLLECT formulae or something for Task A wherein it populates the % in that very R/Y/G column associated to its Heath on project plan and populate a blank for others.

Let me know if you need further clarity.

Best Answer

Answers

  • Julie Fortney
    Julie Fortney Overachievers

    Hi @Gaurav Chauhan,

    You could use a Symbol column to first pull the task's RYG symbol, then set up formulas in each color column to pull the % complete if the symbol is that color.

    Here are the formulas:

    Symbol: =INDEX(COLLECT({Project Plan Status}, {Project Plan Task Name}, Task@row), 1)

    Red: =IF(Symbol@row = "Red", INDEX(COLLECT({Project Plan % Complete}, {Project Plan Task Name}, Task@row), 1))

    Green: =IF(Symbol@row = "Green", INDEX(COLLECT({Project Plan % Complete}, {Project Plan Task Name}, Task@row), 1))

    Yellow: =IF(Symbol@row = "Yellow", INDEX(COLLECT({Project Plan % Complete}, {Project Plan Task Name}, Task@row), 1))

    You'll need to set up the cross-sheet references as shown in { }.

    www.linkedin.com/in/julie-fortney-pmp-lssblackbelt

  • Gaurav Chauhan
    Gaurav Chauhan ✭✭✭✭✭✭
    Answer ✓

    Thank you.

    Actually I wrote this in RGY Columns and it worked great

    =IFERROR(INDEX(COLLECT({% Complete}, {Workstream}, Task1, {Workstream Health}, "Red"), 1), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!