Collect formulae
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
-
Thank you.
Actually I wrote this in RGY Columns and it worked great
=IFERROR(INDEX(COLLECT({% Complete}, {Workstream}, Task1, {Workstream Health}, "Red"), 1), "")
Answers
-
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 { }.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!