Collect formulae

Gaurav Chauhan

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.

Gaurav Chauhan

Thank you.

Actually I wrote this in RGY Columns and it worked great

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

Julie Fortney

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

Thank you.

Actually I wrote this in RGY Columns and it worked great

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