Automatically update current stage

Esperanza Zagal

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

Paul Newcome

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)

Esperanza Zagal

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?

Paul Newcome

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).

Esperanza Zagal

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

Esperanza Zagal

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.

Paul Newcome

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")

Esperanza Zagal

I'm getting a #UNPARSEABLE error message.

Paul Newcome

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(........................................)))

Esperanza Zagal

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

Paul Newcome

Can you copy/paste the exact formula you are using directly from the sheet?

Esperanza Zagal

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!!

Paul Newcome

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")), ", ")