Latest Revision - without pulling Revision History

Kelly Moore

Glad it worked! If you find that our sequencing isn't quite right just shout out to me and we'll continue to tweak it.

Kelly

ssjulien

Thank you, will do!

Any thoughts on this one?

You'll see I have a revision Package column next to each Rev #. Is there a way I could pull the latest Package info into a new column, similar to the latest Rev # and date?

Kelly Moore

I'll play with that for a bit. It might take me a day - I hate when my real job gets in the way of smartsheet. 😅

ssjulien

HAHA!! This really needs to be your full-time job. Luckily I get to play with smartsheet a lot at work. :)

Kelly Moore

Ok, the formula below will work. I was hoping to find one that would be less clunky, but it's difficult to index across a row.

=IF([Latest Rev #]@row="Rev A", [Rev A Package Comment]@row, IF([Latest Rev #]@row="Rev B", [Rev B Package Comment]@row, IF([Latest Rev #]@row="Rev 0", [Rev 0 Package Comment]@row, IF([Latest Rev #]@row="Rev 1", [Rev 1 Package Comment]@row, IF([Latest Rev #]@row="Rev 2], [Rev 2 Package Comment]@row, IF([Latest Rev #]@row="Rev 3", [Rev 3 Package Comment]@row, IF([Latest Rev #]@row="Rev 4", [Rev 4 Package Comment]@row)))))))

I think I got all the parentheses right - I didn't test in a sheet. Replace the column names in the formula with your actual column names

Kelly

ssjulien

Hi Kelly,

Is the beginning of the IF statement trying to pull the "Most Current Rev" info, like this:

=IF([Most Current Rev]@row="Rev A", [Rev A Package]@row, IF([Most Current Rev]@row="Rev B", [Rev B Package]@row, IF([Most Current Rev]@row="Rev 0", [Rev 0 Package]@row, IF([Most Current Rev]@row="Rev 1", [Rev 1 Package]@row, IF([Most Current Rev]@row="Rev 2], [Rev 2 Package]@row, IF([Most Current Rev]@row="Rev 3", [Rev 3 Package]@row, IF([Most Current Rev]@row="Rev 4", [Rev 4 Package]@row)))))))

or from the columns with checkboxes?

=IF([Rev A]@row="Rev A", [Rev A Package]@row, IF([Rev B]@row="Rev B", [Rev B Package]@row, IF([Rev 0]@row="Rev 0", [Rev 0 Package]@row, IF([Rev 1]@row="Rev 1", [Rev 1 Package]@row, IF([Rev 2]@row="Rev 2], [Rev 2 Package]@row, IF([Rev 3]@row="Rev 3", [Rev 3 Package]@row, IF([Rev 4]@row="Rev 4", [Rev 4 Package]@row)))))))

Maybe I have it totally wrong, but neither of these are working. I'm getting the Unparseable error.

I'm confused about the "@row" that is in the formulas - could you explain what that does/means?

Thanks!

Kelly Moore

Hey

Yes, I wanted to pull from your Most Current Rev column (you hadn't showed column names before now in any of your screenshots). Whatever the [Most Current Rev] references, I want it to respond with the comment column that corresponds to that revision. The checkbox wouldn't help you - if you put that in as an response it would only show a one or a zero.

The first equation you posted with the [Most Current Rev] is the right one. Look at your formula - you have an extra bracket in your [Rev 2] IFs instead of an ending quote mark. I fixed it in the formula below

=IF([Most Current Rev]@row="Rev A", [Rev A Package]@row, IF([Most Current Rev]@row="Rev B", [Rev B Package]@row, IF([Most Current Rev]@row="Rev 0", [Rev 0 Package]@row, IF([Most Current Rev]@row="Rev 1", [Rev 1 Package]@row, IF([Most Current Rev]@row="Rev 2", [Rev 2 Package]@row, IF([Most Current Rev]@row="Rev 3", [Rev 3 Package]@row, IF([Most Current Rev]@row="Rev 4", [Rev 4 Package]@row)))))))

The @row is a way designating a row number. It is the most robust way of writing formulas - try not to use actual row numbers unless a row needs to be specifically called out- even then I have a workaround where I seldom have to use the actual row number.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

Kelly

ssjulien

Kelly,

It works! I see the issue now. I think after I added 400+ submittals I was getting too bleary-eyed. I can't thank you enough for all your time and help. When I submitted this question, I had no idea it would take such complicated formulas. This has shown me so much of what I don't know. I have a lot of studying to do - I will be referencing back here a lot to absorb everything you've taught me. Hopefully I will be able to write some formulas like this in the future!

Sarah

Kelly Moore

So glad everything works! I appreciated the questions that you had about my formulas - that helped me ensure you were following along. Asking those questions will help you learn new formulas even faster.

See you in the community

Kelly