Latest Revision  without pulling Revision History
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 autoupdate 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!
Best Answer

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
Answers

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

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

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?

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

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!

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 metrictype sheet so we're now using cross sheet references?
Kelly

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!

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

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!

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?

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!

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?

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?

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

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!
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 455 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives