Latest Revision - without pulling Revision History

ssjulien

Hello!

I could use some help problem solving this. I have a smartsheet tracking revision number and date for about 240 rows of submittals.

I'm trying to come up with a formula that will tell me the latest revision for each row and will auto-update as new revisions are added.

The goal being to have a clean print out which would show Drawing Package, Description, latest revision # & date for each row, without showing the entire revision history. I think I can figure out how to pull the date in once I get this initial equation, so no need to worry about that for now.

Using the above screenshot as an example, I would want it to pull:

C1.03 - Rev 1

C2.00 - Rev 0

C2.01 - Rev 2

Currently, I'm trying to pull in the information based on which of the checkmarks are selected but not getting too far. We are open to changing how the information is entered if you think of something that would make more sense than using checkmarks.

Thanks for your help!

Kelly Moore

Hey @ssjulien

Here's one approach that may work for you

=IF(COUNTIFS([Rev 0]@row:[Last Rev Date column in your row]@row, ISDATE(@cell)) - 1 >= 0, "Rev " + VALUE(COUNTIFS([Rev 0]@row:[Last Rev Date column in your row]@row, ISDATE(@cell)) - 1))

*Don't forget to change the name of the last Date column in the formula to match your actual last date column name

Will this work for you?

Kelly

Kelly Moore

Hey @ssjulien

Here's one approach that may work for you

=IF(COUNTIFS([Rev 0]@row:[Last Rev Date column in your row]@row, ISDATE(@cell)) - 1 >= 0, "Rev " + VALUE(COUNTIFS([Rev 0]@row:[Last Rev Date column in your row]@row, ISDATE(@cell)) - 1))

*Don't forget to change the name of the last Date column in the formula to match your actual last date column name

Will this work for you?

Kelly

ssjulien

Hi Kelly,

Thank you! It seems like we're getting somewhere, but it's not working where I have greater than Rev 1:

Here's the formula:

=IF(COUNTIFS([Rev 0]@row:[Rev 4 Date]@row, ISDATE(@cell)) - 1 >= 0, "Rev " + VALUE(COUNTIFS([Rev 0]@row:[Rev 4 Date]@row, ISDATE(@cell)) - 1))

I'm not sure how to fix it - any thoughts?

Thanks,

Sarah

Kelly Moore

Hmm, it works perfectly in my test sheet. Are all of your Date columns actual Date formatted fields? Your [Rev 2 Date] does not have the leading zero for a month. How are these dates being determined?

ssjulien

Wow, you're a genius! My column was Text not Date. Once I updated it, I got the correct Rev to show. Thank you so much!!!!

ssjulien

Now I want to add another column to show the Latest Revision date. Can I modify your formula to pull in the date of the latest Rev # as well? or do you think it would need to be a completely different formula? Unfortunately I can't read the formula well enough to know how to change it. I'm going to study VALUE/COUNTIFS formulas right now!

Thanks!

Kelly Moore

LOL, thanks - but no genius. That's why the community will almost always ask for a screenshot with any question.

I had to add the VALUE function around the COUNTIFS because there was text in the formula (the "Rev" part). Smartsheet would have tried to use the COUNTIFS as text - and COUNTIFS isn't a text function. So there would have been an error. I had to force smartsheet to use the COUNTIFS as an actual value. You use VALUE when you want to force the result to be treated as an actual number (and not text that happens to look like a number). If you didn't want the word 'Rev' to be added to the result we can delete that part - then we could also remove the VALUE from being wrapped around the COUNTIFS.

To answer your latest question. Yes we will need another formula - no problem. I'm a bit unclear of what date I am pulling. Do you want the max date for each row, regardless of what stage, or, of all the Rev 1's in the sheet, the max date of that, then the Rev 2s, etc. Also, if the latter, is this on the same sheet or are you pulling info into a metric-type sheet so we're now using cross sheet references?

Kelly

ssjulien

Kelly, thank you for the additional explanation! That's so helpful. I would like to keep the "Rev" in there, so let's leave that one alone.

The columns show each revision and the date of each revision. So when I pull the latest Rev date, I want to also pull the date that latest Rev was done. I think it's the first option you mentioned. Everything on this same sheet!

In the screenshot, I highlighted the dates I want it to pull for each row:

Thanks!

Kelly Moore

Gotcha

You'll need to add a column for the last revision date. Make sure it's a date column 😉

=MAX(COLLECT([Rev 0]@row:[Rev 4 Date]@row, [Rev 0]@row:[Rev 4 Date]@row, ISDATE(@cell)))

Does that work for you?

Kelly

ssjulien

Wow, you're like a wizard! The dates are in, looks like they are working!

Couple of bonus questions if you have the time/patience/energy,etc. :)

1) what if we have some revisions that are A/B, any way to pull in letters instead? I was trying to mess around with the formula and it will pull the info for A/B and show them as 0/1 - which I can work with, but I was curious.

=IF(COUNTIFS([Rev A]@row:[Rev 4 Date]@row, ISDATE(@cell)) - 1 >= 0, "Rev " + VALUE(COUNTIFS([Rev A]@row:[Rev 4 Date]@row, ISDATE(@cell)) - 1))

2) 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?

Thanks!

Kelly Moore

So are you saying if you have Change Order Response in Rev 1 you would like it to say Rev 1A instead of just Rev 1? Or is it a separate column and you also want Rev 1A to be designated somewhere else? By the time it says Change Order Response, does that mean the change order has been approved/accepted?

What is the difference between A and B? If there is a change request in Rev 2 does that make it B and so on?

ssjulien

Unfortunately we're in a situation where numbering system A/B/C vs 0/1/2/3 was inconsistent. Sometimes in the numbering system it reads A/B/0/1/2/3 (A being a draft file or client copy).... other times the alpha overlaps as A is same as 0 and B is same as 1, but no, it wouldn't be 1A, 1B, etc in any case.

I'm attempting to clean up the records!

Kelly Moore

Help me understand the output you are looking for - can you mock it up either in smartsheet or excel so I can see what you are looking for?

ssjulien

Sure! so it would be just like how we did the other one, but with A and B instead of numbers:

The way I have the smartsheet columns right now, it goes from A, B, 0, 1, 2, 3, 4 (will add on after 4). Most of the submittals do not have A/B.

Does that make more sense?

Kelly Moore

Let's see if this will work for you. This formula will replace the original Rev formula I gave you. I assumed that a date in [Rev B Date] wouldn't happen before [Rev A Date] was completed - that is, the dates get completed in sequence from left to right.

=IF(AND(ISDATE([Rev A Date]@row), NOT(ISDATE([Rev B Date]@row))), "Rev A", IF(AND(ISDATE([Rev A Date]@row), ISDATE([Rev B Date]@row), NOT(ISDATE([Rev 0 Date]@row))), "Rev B", IF(COUNTIFS([Rev 0]@row:[Rev 4 Date]@row, ISDATE(@cell)) - 1 >= 0, "Rev " + VALUE(COUNTIFS([Rev 0]@row:[Rev 4 Date]@row, ISDATE(@cell)) - 1), "")))

The main thing to always remember about nested IFs is that the IFs will progress until the first true statement is found. Once a true is found, the formula stops. The sequence of the IFs therefore become important. The sequence is one of the most common mistakes I see in nested IF statements. That being said, read the logic of my IFs below and see if, based on your process flow, the logic in the formula is going to work.

In english, the formula says

If [Rev A Date] has a date but [Rev B Date] doesn't have a date, Rev A *This term doesn't have any reference to what might be happening in [Rev 0 Date]. I'm hoping if [Rev A Date] has a date [Rev 0 Date] wouldn't have a date? Or is it [Rev 1 Date] I should be concerned with? Or, is it when [Rev A Date] or [Rev B Date] is the max date of the row?

If both [Rev A Date] and [Rev B Date] have dates, but [Rev 0 Date] doesn't have a date, Rev B

IF [Rev 0 Date] has dates, start the 1st formula I gave you.

Let me know what we need to tweak

Kelly

ssjulien

Hi Kelly,

Yes, your assumption about the sequence is correct, at least, that's my assumption as well.

The formula worked as is!! Thank you and thank you for the detailed "in english" - I'm really learning so much from you, this is amazing! I appreciate you so much!