Latest Revision - without pulling Revision History

2»

Answers

  • Kelly Moore
    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
    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
    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
    ssjulien ✭✭✭

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

  • Kelly Moore
    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
    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
    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.

    Kelly

  • ssjulien
    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
    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