Most Recent Entry Formula Help

Michelle Fayed

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.

Genevieve P.

Hi @Michelle Fayed

The 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(COLLECT({Date Column}, {Station Column}, [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!

Cheers,

Genevieve

SkiPatrolScott

@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

If I remember, I can pre-fill the Location data, 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.

Hi @SkiPatrolScott

Yes! For the date you'll want to simply use a MAX(COLLECT, identifying the Location:

=MAX(COLLECT({Date Column}, {Location Column}, Location@row))

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}, Location@row, {Date Column}, Date@row), 1)

=INDEX(COLLECT({Time}, {Location Column}, Location@row, {Date Column}, Date@row), 1)

Cheers,

Genevieve

SkiPatrolScott

@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 entering in military time time 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

SkiPatrolScott

@Genevieve P. @Paul Newcome just 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.

Genevieve P.

Hiya! Just to chime in, it looks like Kelly responded on your other post @SkiPatrolScott as well:

https://community.smartsheet.com/discussion/100197/need-max-date-and-max-time-for-specific-location

SkiPatrolScott

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

Genevieve P.

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 the distinct names 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 would love to come for a Ski trip... however I may be a bit far away in the south of England. Perhaps @Paul Newcome is closer? ⛷️

Cheers,

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

SkiPatrolScott

@Paul Newcome @Genevieve P.

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

SkiPatrolScott

@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, Patroller@ROW , 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.

Hi @SkiPatrolScott

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!

Cheers,

Genevieve

SkiPatrolScott

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

Haha! Amazing. I'd settle for a ski run named after me instead 😉

Yes, let us know how it goes!