Most Recent Entry Formula Help

@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.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

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.

@Genevieve P.

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

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!

@Paul Newcome@Genevieve P.

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