Previous Values as historical values

Jona_g28

Hello all,

I am in need of a way to store previous values (mostly symbols showing project progress) into a sheet that would allow for comparison if needed. The main sheet would have current status and a harvey ball symbol either green, red or yellow. Then next week, I want to update based on project status but I don't want to lose sight of previous value. Anyone has a workaround this?

Thanks in adcance!

Paul Newcome

Have you looked into the Copy Row automation?

Jona_g28

I don't think that would help because current values would become previous values next time I update this sheet. Copy rows wouldn't help in that case.

What I want to do is show that current week status is green - next week this value will populate a previous status column and I will have a new value under current week

Paul Newcome

In that case you would still need to copy the row over to another sheet (copy row creates static data, so it is not going to update on sheet two when you update it on sheet one). You are also going to need some type of unique identifier on each row as well as an auto-number column in sheet 2. Then you would use an INDEX/MATCH to pull in the status column matching on a MAX/COLLECT that pulls in the highest auto-number from sheet two based on that unique identifier.

=INDEX({Sheet 2 Status}, MATCH(MAX(COLLECT({Sheet 2 Auto Number}, {Sheet 2 Unique ID}, @cell = [Unique ID]@row)), {Sheet 2 Unique ID}, 0))

Jona_g28

Thank you Paul for your answer, however I don't think this would be ideal. My situation requires 4 statuses to be called by the formula because I am tracking Current Scope, Schedule, Budget and resources. I will try to have the workflow created and triggered to run on a custom date and then rely on manual work to complete the rest. I don't think there's a way to automate what I need at this point...

Matt Kirby

@Jona_g28 I have a very similar use case, did you find a solution?

Jona_g28

Hi @Matt Kirby I did find a way.

I used the steps described by @Genevieve P. on this discussion.

https://community.smartsheet.com/discussion/101913/how-to-keep-the-previous-historical-project-planned-progress-statisplanned-progress