Most Recent Entry Formula Help
Hello to all Buddha master smartsheeters who have taken the time to help me.
I need to create a formula that reflects the status of an item at a particular location based on the most recent entry for that location on the master sheet, located on a separate tracking sheet. I've attached images to help.
The larger image (with the yellow) shows my master sheet that contains the various different Station #'s and the Open Date columns. The smaller image in where I need to create 2 formulas, the first in the Unit Status Column that reports the Unit Status from my master sheet based on the most recent entry for that station. The second is a formula that reports back the corresponding open date to the most recent entry for a particular station.
Any help would be so appreciated! Thank you in advance to those who may provide assistance.
Answers
-
Genevieve P. Employee AdminThe Open Date will be the easiest one, so lets start with that. We can use a MAX(COLLECT to find the MAX date (most recent date) based on criteria:
= MAX(收集({日期列}、{站列}(Station #]@row))
Then for the Status, we can use an INDEX(COLLECT to bring back the status, based on the MAX date that we already found with the other formula:
=INDEX(COLLECT({Status}, {Station Column}, [Station #]@row, {Date Column}, [Open Date]@row), 1)
See:COLLECT Function/MAX Function/INDEX Function
Let me know if this makes sense and works for you!
欢呼,
Genevieve
-
@Genevieve P.I think I will try this formula for the Dispatch Log you helped me with before. In the [Event] for the entry "Sign Line Status" I will need to show the most recent Entry based on [Location], There will be about 10 Sign Lines that we track. I will want to show there current status as OPEN or CLOSED and when that action happened with a Date and Time.
So the Sheet that will pull this information from the Daily Log will Show (to be displayed in a Dashboard)
[Location], [Status], [Date], [Time]
Name Upper, Open, Jan 18 2023, 1000
Name Middle, Open, Jan 20 2023, 1000
Name Lower, Closed, Jan 21, 2023, 1630
如果我记得,我可以预先填充的位置数据,then I will need to use a sheet reference formula to extract the Status, Date, Time.
And if I correctly identify, the most recent entry should display?
-
Genevieve P. Employee Admin
Yes! For the date you'll want to simply use a MAX(COLLECT, identifying the Location:
=MAX(COLLECT({Date Column}, {Location Column},[email protected]))
Then you can reference this date formula in your other INDEX(COLLECT formula, deleting and replacing the first reference with the column you want to return:
=INDEX(COLLECT({Status}, {Location Column},[email protected], {Date Column},[email protected]), 1)
=INDEX(COLLECT({Time}, {Location Column},[email protected], {Date Column},[email protected]), 1)
欢呼,
Genevieve
-
@Genevieve P.unfortunately I cannot guarantee that the person doing the inputting will enter the action as it happens (poor quality control). My fear is that someone might enter (01/29/2023, 1100, RUN, OPEN )and then remember oh yea, that was closed earlier, I'll just record that entry so its in the log... (01/29/2023, 0750, RUN, CLOSED). So that in our log even though the TIME 0750 should be first, if they do not run a sort, it will be the MAX date and be incorrectly showing RUN as closed. Is there a way I could create a formula that would look at both? or sequence in a helper field that would recognize the date and time?
While it may not happen often, and the inputter should be more responsible, it could happen.
Since SS does not have a input mask, or time, we are just enteringin military timetime as 0800,1300,1636... however when I did a SORT by DATE, SORT by TIME, the 1300 and 1636 were above the 0800. Is there a reason for this? one thought I had for the Helper Field would be if I could tell SS to record the TIME field as an ABSOLUTE number so 0800 would auto to 800, and 1300 would remain 1300.
The other tips for our VIOLATION is working great, let me know when you want to come out and ski... I will definitely hook you up
-
@Genevieve P.@Paul Newcomejust when I think I got it... the formula that I use does not show the MAX date, it only shows the first DATE, so when we open a RUN, the formula thinks that is the MAX even though later we close the run...
I tried to create in my PRIMARY the formula below, this actually worked to properly sort...
But when I try to reference this in my Most Current, it comes back as ZERO, see below
I tried to use the same formula to display the DATE, exchanging {date} with {Primary} but no love....
-
Paul Newcome ✭✭✭✭✭✭It comes back as zero from the MAX function because your formula is actually outputting a text string. Try removing the "-" and wrapping the whole thing in a VALUE function to output an actual number. You can still sort on it and the MAX function can now pick it up.
thinkspi.com
-
Genevieve P. Employee Admin
Hiya! Just to chime in, it looks like Kelly responded on your other post@SkiPatrolScottas well:
-
Hey Genevieve, I'm actually getting buy in from the Suits, below is data extracted from our route sheets (luckily we've been busy). I am extracting when someone is a [LEAD] and a [SECOND] for a [Route Total], the [Patroller Rank] is a EQ of the [Route Total] column. Is is possible to display the Rank as a %?
I am actually using VLOOKUP from this Sheet in another Sheet listing all the routes... Keep hoping you are planning a trip to come skiing so we can show you in person some of our SS applications...
Right now I am just taking [Patroller Rank]/N and then showing the number as a percentage (where N is total number of participants. I just worry I will forget to up date N. I am then running conditional formatting to highlight the Patrollers between %. lets just say there are some equity issues.....
-
Genevieve P. Employee Admin
Hey@SkiPatrolScott!
That sheet is looking good
So, you need "N" (where N is total number of participants) to auto-update as new participants join, is that correct?
If so, are each of your participants listed in the [Participant] column on the left? Could we divide it instead by thedistinctnames in that column? We'd only need Distinct if there's a possibility of repeats.
=RANKEQ([Route Total]@row, [Route Total]:[Route Total], 0)/ COUNT(DISTINCT([Participant]:[Participant]))
Let me know if I've misunderstood!
PS - I wouldloveto come for a Ski trip... however I may be a bit far away in the south of England. Perhaps@Paul Newcomeis closer?⛷️
欢呼,
Genevieve
-
Paul Newcome ✭✭✭✭✭✭
I agree the COUNT/DISTINCT is the way to go to get "N" in this case.
As for me being closer... I live at the base of the Appalachian Mountains and am only about 30 minutes away from a pretty popular ski resort (as well as a spattering of other, smaller places) and yet have never been skiing. I did go snow tubing once though. Hahaha. But I would still be excited to see Smartsheet implementations in person!
thinkspi.com
-
I went a slightly different route. There should be no duplicates, but what I was trying to capture was the TOP percentile, you know the 1%, so by using the RANK I am able to divide by the Count (which will update automatically as I add names... Not sure what the equity in Routes should be, but now the Suits know there is quite a bit of separation between 47 patrollers...
-
I'm not sure how to even post this question...But it is kinda on the same string of what I'm working on. We are using SS to track our Avalanche Route Assignments. The previous post, I have been able to pull and count routes that each patroller goes on and the number of shots they use.
Give em a little and they want more.
The Suits would like to identify who is a Lead Q , Second Q, Priority T, for our routes (32 Total) I have a form built that will populate the sheet. Names have been whited out. This sheet will be updated as patrollers become proficient (we are documenting that info in a SS taskbook).
Ideally I would like to be able to just add a new row (even if the patroller info has already been updated) but we want to always be able to just pull up a patroller and access their current Quals... so here is where my question is, the Sheet that this Sheet will feed, will look like the one below. What I am hoping to be able to do, is have a formula that will pull from the above Sheet,[email protected], if {Lead Q} contains "Bear Wallow" Display "L", or if {Second Q} contains "Bear Wallow" Display "S", or if {Priority} contains "Bear Wallow" Display "T". I am guessing I would need to create the formula for each ROUTE under the appropriate heading (not a big deal). But can SS look at three rows? Can I prioritize the rows incase there is duplicate values in each row?
I was able to figure out how to extract the route counts and who was the lead in another sheet.. its still under construction but its pulling data from a few different places. Unfortunately I dont think I can condition the Sheet to color based on a number as I have added "L" and "S". Just wanted to share this sheet with you.
-
Genevieve P. Employee Admin
I actually agree that a COUNTIFS formula is the way to go!
If you nest that in IF statements, then yes, you can prioritize the order in which the L, S, T, or N displays.
For example:
=IF(COUNTIFS({Date Column}, [DATE UPDATE]@row,{Lead Q column}, HAS(@cell, "Bear Wallow")) > 0,"L",IF(COUNTIFS({Date Column}, [DATE UPDATE]@row,{Second Q column},HAS(@cell, "Bear Wallow")) > 0, "S", IF(COUNTIFS({Date Column}, [DATE UPDATE]@row,{Priority column},HAS(@cell, "Bear Wallow")) > 0, "T", "N")))
The order in which you have the IF statements determines Priority. In the example above, if "Bear Wallow" appears in all three columns, it will only return "L" because that's the first match it finds.
If you want the Priority column to be the first one looked into, swap around the order:
=IF(COUNTIFS({Date Column}, [DATE UPDATE]@row,{Priority column}, HAS(@cell, "Bear Wallow")) > 0,"T",IF(COUNTIFS({Date Column}, [DATE UPDATE]@row,{Second Q column},HAS(@cell, "Bear Wallow")) > 0, "S", IF(COUNTIFS({Date Column}, [DATE UPDATE]@row,{Lead Q column},HAS(@cell, "Bear Wallow")) > 0, "L", "N")))
Let me know if that makes sense and will work for you!
欢呼,
Genevieve
-
@Genevieve P.seriously thinking about renaming one of my girls to Genevieve, I had to do some tweaks as we (ME) were slightly on the wrong row.. but I got it working. May take a few days but I will send you a pic of the final product.
-
Genevieve P. Employee Admin
Haha! Amazing. I'd settle for a ski run named after me instead
是的,我们知道它goes!
Help Article Resources
Categories
Check out theFormula Handbook template!
Double check your cross sheet references. Make sure they are all single columns by clicking on the appropriate column header.<\/p>
<\/p>
When doing this, give the sheet a little time to load before selecting the column. Sometimes moving too fast allows you to select a column header before the sheet fully loads in the creator window. Then when the sheet does finally completely load in, the selection automatically reverts to the home cell (top right corner). This happens to me quite a bit when I am in a hurry.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106940,"type":"question","name":"IF cell CONTAINS one\/two\/three different values, return corresponding values","excerpt":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\", \"Termination\", \"New\" Currently the formula works for single values with the following formula =IF([Type of Project]@row = \"Consolidation\", \"🝢\",…","snippet":"Hello community, I am attempting to return single or multiple values depending on a multiple dropdown column. My dropdown column contains criteria \"Consolidation\", \"Reduction\",…","categoryID":322,"dateInserted":"2023-06-27T09:24:42+00:00","dateUpdated":null,"dateLastComment":"2023-06-27T12:11:45+00:00","insertUserID":143463,"insertUser":{"userID":143463,"name":"Sam Swain","url":"https:\/\/community.smartsheet.com\/profile\/Sam%20Swain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T20:08:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":151203,"lastUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":56,"score":null,"hot":3375727587,"url":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106940\/if-cell-contains-one-two-three-different-values-return-corresponding-values","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106940,"commentID":382505,"name":"Re: IF cell CONTAINS one\/two\/three different values, return corresponding values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382505#Comment_382505","dateInserted":"2023-06-27T12:11:45+00:00","insertUserID":151203,"insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-27T12:11:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/Q6RJIERQGF1Y\/screenshot-2023-06-27-at-10-22-29.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-06-27 at 10.22.29.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-27T10:17:00+00:00","dateAnswered":"2023-06-27T10:12:21+00:00","acceptedAnswers":[{"commentID":382490,"body":"
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/0T1JMLQOPB6K\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
![https:\/\/us.v-cdn.net\/6031209\/uploads\/N4ZJ2IRDN561\/primarydisp.jpg<\/a><\/p>https:\/\/us.v-cdn.net\/6031209\/uploads\/X1XWZIKYXZQ5\/primarymax.jpg<\/a><\/p>The formula did work to pull the most recent date, its just that this Run was opened at 1107 and the formula did not decipher that....<\/p>","bodyRaw":"[{\"insert\":\"I need to be able to parse out an entry based on Latest Entry, most often this entry will have an open and close., sometimes multiple times a day. I want to be able to have a separate sheet that displays the most recent event. Thought I had it, but realized the sheet stopped at the first MAX date and did not reference the most recent entry. Created a formula to take the DATE field and display as 230125 and then added the TIME so the final looks like 2301250825, works great to sort but am getting a \\\"0\\\" any time I try to find a max value. Is it because it is a Formula? note in the example below I did have +\\\"-\\\"+ between date and time.\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/N4ZJ2IRDN561\\\/primarydisp.jpg\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/N4ZJ2IRDN561\\\/primarydisp.jpg\"}},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/X1XWZIKYXZQ5\\\/primarymax.jpg\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/X1XWZIKYXZQ5\\\/primarymax.jpg\"}},{\"insert\":\"\\n\"},{\"insert\":\"The formula did work to pull the most recent date, its just that this Run was opened at 1107 and the formula did not decipher that....\\n\"}]","format":"rich","dateInserted":"2023-01-26T04:52:14+00:00","insertUser":{"userID":154303,"name":"SkiPatrolScott","url":"https:\/\/community.smartsheet.com\/profile\/SkiPatrolScott","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!yVFr8iYd8eI!3FfqP-5CibI!uiok-DAQbwE","dateLastActive":"2023-04-05T01:45:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/100197\/need-max-date-and-max-time-for-specific-location","embedType":"quote","name":"Need MAX Date, and MAX Time, for specific Location"}"> https://community.smartsheet.com/discussion/100197/need-max-date-and-max-time-for-specific-location](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/N4ZJ2IRDN561\/primarydisp.jpg\"){kind=link}
![https:\/\/us.v-cdn.net\/6031209\/uploads\/X1XWZIKYXZQ5\/primarymax.jpg<\/a><\/p>The formula did work to pull the most recent date, its just that this Run was opened at 1107 and the formula did not decipher that....<\/p>","bodyRaw":"[{\"insert\":\"I need to be able to parse out an entry based on Latest Entry, most often this entry will have an open and close., sometimes multiple times a day. I want to be able to have a separate sheet that displays the most recent event. Thought I had it, but realized the sheet stopped at the first MAX date and did not reference the most recent entry. Created a formula to take the DATE field and display as 230125 and then added the TIME so the final looks like 2301250825, works great to sort but am getting a \\\"0\\\" any time I try to find a max value. Is it because it is a Formula? note in the example below I did have +\\\"-\\\"+ between date and time.\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/N4ZJ2IRDN561\\\/primarydisp.jpg\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/N4ZJ2IRDN561\\\/primarydisp.jpg\"}},{\"insert\":\"\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/X1XWZIKYXZQ5\\\/primarymax.jpg\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/X1XWZIKYXZQ5\\\/primarymax.jpg\"}},{\"insert\":\"\\n\"},{\"insert\":\"The formula did work to pull the most recent date, its just that this Run was opened at 1107 and the formula did not decipher that....\\n\"}]","format":"rich","dateInserted":"2023-01-26T04:52:14+00:00","insertUser":{"userID":154303,"name":"SkiPatrolScott","url":"https:\/\/community.smartsheet.com\/profile\/SkiPatrolScott","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!yVFr8iYd8eI!3FfqP-5CibI!uiok-DAQbwE","dateLastActive":"2023-04-05T01:45:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":true,"showPostLink":true,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/100197\/need-max-date-and-max-time-for-specific-location","embedType":"quote","name":"Need MAX Date, and MAX Time, for specific Location"}"> https://community.smartsheet.com/discussion/100197/need-max-date-and-max-time-for-specific-location](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/X1XWZIKYXZQ5\/primarymax.jpg\"){kind=link}