Automatically update current stage
Hi,
I am trying to track progress through various stages of publication (the columns titled approval, data collection, analysis...accepted). I thought the health bubbles may be a good way to quickly visualize progress but it is confusing the card view as it can only organize per specified column at a time. What would be the best way to format so that I'm able to generate a report of the current stage (designated by a yellow or red bubble) of any project (presented in rows)?
I thought adding a "current stage" column would help, though, I would want the "current stage" column to automatically populate the name of the column(s) that have a yellow or red bubble.
Feedback welcome!
Esperanza
Answers
-
The best way I can think of to automate it would be to create a helper ROW across the top where you manually enter each of the column names for the different stage columns.
Then you could use something along the lines of...
=INDEX(Approval$1:Submitted$1, 1, COUNTIFS(Approval@row:Submitted@row, "Green") + 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you Paul, I feel like that's on track. Would the helper row be above the first row, which the screenshot shows as "Main outcome"?
and if I'm interested in stages approval to accepted would the code read
=INDEX(Approval$1:Accepted$1, 1, COUNTIFS(APPROVAL@row:Submitted@row, "Green")+1)
sorry if this sounds like a silly question, but I don't know what $1 indicates?
-
You are correct in the first two.
The last one... $ locks in the reference. If it is before the column name, it will lock in that column name. If it is before the row number then it locks in the row number. So as you dragfill or as auto-fill pulls the formula into new rows, it will always reference row 1.
This means that the INDEX function will always pull data from the first row based on the data in the current row (@row).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you Paul for your input.
I'm struggling to understand the formula as written. If I am interested only in yellow or red circles do I change "Green" to "Yellow or Red"?
Ultimately, I'd like some thing that populates the column title (Approval:Accepted) of each yellow or red bubble per row. For example, for the pictured row 1, it would read "Final" as the current stage, "Analysis" and"Outline" for the 2nd row, etc.
Thank you!
Esperanza
-
I was thinking of something like this in the Current Stage column:
=IF(Approval@row = "Yellow", "working on Approval"), IF(Data collection@row="Yellow", "working on Data Collection")
...and so on to "Accepted"
What I have doesn't work though. Trying to figure out why.
-
When using a column name that has spaces, special characters, or numbers in it, you need to wrap the column name in square brackets.
=IF(Approval@row = "Yellow", "working on Approval"), IF([Data collection]@row="Yellow", "working on Data Collection")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I'm getting a #UNPARSEABLE error message.
-
Sorry. I missed that you are also closing out each of your IF statements individually. You should string them all together and then close them all at the end.
=IF(Approval@row = "Yellow", "working on Approval", IF([Data collection]@row="Yellow", "working on Data Collection", IF(........................................)))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you Paul.
I'm fortunately I'm getting the error message with and without the paranthesis bringing the IF statement at the end. To complicate matters, I'd also like to add to the statement that the Current Stage should read "working on [column]" if the circle is yellow or red. Would this be possible?
Thank you, and I'm sorry for the challenge!
Esperanza
-
Can you copy/paste the exact formula you are using directly from the sheet?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Ok, think I got it to work after reviewing the parentheses (equation below).
Some rows are working on multiple yellow bubbles (working on multiple stages at once), I would like the current stage column to reflect that. For example saying, "working on Analysis", "working on Outline", etc. Is that posible?
Also I would like Yellow and Red bubbles to generate a "working on..." command.
=IF(Approval@row = "Yellow", "working on Approval", IF([Data collection]@row = "Yellow", "working on Data collection", IF(Analysis@row = "Yellow", "working on Analysis", IF(Outline@row = "Yellow", "working on Outline", IF(Draft@row = "Yellow", "working on Draft", IF(Final@row = "Yellow", "working on Final", IF(Submitted@row = "Yellow", "Submitting", IF(Resubmission@row = "Yellow", "working on Resubmission", IF(Accepted@row = "Yellow", "waiting for notice of Acceptance")))))))))
Thank you!!
-
Yes. You could separate your IF statements and "add" them together into a string.
=IF(Approval@row = "Yellow", "working on Approval, ") + IF([Data collection]@row = "Yellow", "working on Data collection, ") + IF(...................................
Or you to have an output of
"Working On: Analysis, Outline, ..............(continue listing out yellow/red columns for the row)"
="Working On: " + IF(Approval@row = "Yellow", "Approval, ") + IF([Data collection]@row = "Yellow", "Data collection, ") + ...................................
And yet another option (which I feel provides the cleanest output) would be to leverage the helper row outlined above and then use a JOIN/COLLECT to pull those that are either yellow or red.
="Working On: " + JOIN(COLLECT([First Color Column]$1:[Last Color Column]$1, [First Color Column]@row:[Last Color Column]@row, OR(@cell = "Red", @cell = "Yellow")), ", ")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!