Create a formula that returns the current project phase in the project summary metadata sheet.
The phases in my project plan are Level 1. The formula should return the text value of the phase (task name) that has the least % started, NOT including phases at 0% or at 99% and higher.
=INDEX({Task Name}, MATCH(MAX(COLLECT({Level}, {Level}, AND(@cell, 1), {Level},1) AND(MATCH(MAX(COLLECT({Project 10 - % Complete}, {Project 10 - % Complete}, AND(@cell < 0.99, @cell > 0))), {Project 10 - % Complete}, 0)))))
It works, until I add in the Level 1.
Anyone have a suggestion?
Thanks
Best Answer
-
I would suggest an INDEX/COLLECT like so:
=INDEX(COLLECT({Testing Phase - Task Name}, {Testing Phase - % Complete}, AND(@cell < 0.99, @cell > 0), {Testing Phase - Level}, @cell = 1), 1)
Answers
-
Hey @Melitta
Try this
=INDEX({Task Name}, MATCH(MAX(COLLECT({Project 10 - % Complete}, {Project 10 - % Complete}, AND(@cell < 0.99, @cell > 0), {Level}, @cell = 1)), {Project 10 - % Complete}, 0))
Will this work for you?
Kelly
-
That gets a little bit closer, Thank you @Kelly Moore
It returns the Task Name, Level = 0 rather than the Task Name, Level =1 which is the phase level.
=INDEX({Testing Phase - Task Name}, MATCH(MAX(COLLECT({Testing Phase - % Complete}, {Testing Phase - % Complete}, AND(@cell < 0.99, @cell > 0), {Testing Phase - Level}, @cell = "1")), {Testing Phase - % Complete}, 0))
I feel as if this is so close and I can't see it.
-
I would suggest an INDEX/COLLECT like so:
=INDEX(COLLECT({Testing Phase - Task Name}, {Testing Phase - % Complete}, AND(@cell < 0.99, @cell > 0), {Testing Phase - Level}, @cell = 1), 1)
-
That works. I also realized I had an error in my project plan that was causing a problem.
I love crowd sourcing a solution!
Thanks for your time.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!