Most Recent Update -- Report or Sheet

SkiPatrolScott

@Genevieve P. @Paul Newcome

We are beginning to use our SS Log to record when we open and close terrain. We want to collect the when, ie our [Date] and [START Time] (still getting requests for a time stamp option). We then want to show the Terrain's current Status as Open or Closed. I am hoping to have a separate sheet and possibly Dashboard. I tried to work some formulas but either get "0" or Invalid or .... Below is the SHEET we are using to collect the information. It could be that a section of Terrain would open and close and open throughout the day (not common but possible).

I would like to collect the most recent data in the sheet below, So that the [Sign Line] would already be filled out and Static, but the date and status would be Dynamic based on the updates to the sheet above.

Any help would be great. Also the first sheet was displayed with a filter, there is a significant amount of other data being recorded, but we wanted to keep this info in the Daily Log so that we are not opening and closing sheets....

Scott

SkiPatrolScott

Hey @Genevieve P. and @Paul Newcome , I think I figured it out, i will post the formula later....

Paul Newcome

If you are filling out the main sheet via form, you could have the form populate at the top of the sheet and then use a basic INDEX/MATCH on the location. Since the INDEX/MATCH stops on the first match and the most recent is at the top, you should pull in the "Current Status" for that location.

SkiPatrolScott

@Paul Newcome I am currently using the 2 formulas in the pics below, I believe it is pulling the Most Current Date, and then indexing current status. My fear is that the entries do not always follow the sequential order as they happen. Someone might be playing catch up and enter the open/close status opposite of when it happened and then not run the SORT. So is it possible to create a formula that factors in the most recent date, based on the start time? The #INVALID VALUE, there is currently no entry to reference...

Paul Newcome

The INVALID VALUE error there in the first screenshot indicates that the COLLECT function is not picking up on any matches.

SkiPatrolScott

yeah @Paul Newcome I can only build the sheet, if the SUITS don't hold the inputters accountable, that is the product they get....

Paul Newcome

Honestly that is generally how I put it to clients (but a little more "friendly"). Haha.

I have been known to send an email that just says the error is due to some inconsistencies in data entry.

One way to avoid this would be to leverage the DISTINCT function in your metrics sheets. Insert a text/number column that I usually just call "Number" and manually enter the numbers 1 through however many you want. Then you can use an INDEX/DISTINCT combo to pull in a list of each entry (not duplicated). From there you can reference this to get your metrics. This helps call out those inconsistencies without actually breaking anything.

For example... Why does David Smith only have 3 open action items? Because someone else assigned some actions to him as "Dave Smith" which happens to be further down the list.

Everything is there. Nothing is broken. But it still helps to highlight inconsistencies without having to dig too much through error messages and missing data.