INDEX MATCH is overridden by new entries

Tobiah

Hello,

I'm trying to create a formula that will identify the [Unit] in Sheet 1 that have confirmed [Today Entry] from Sheet 2. The issue is when a new entry comes through Sheet 2 where [Unit] matches it will override the previous INDEX MATCH action, removing the entry from the list of [Today Entry] in Sheet 1 even though they still technically meet the [Today Entry] criteria (which is time based). Any way I can ensure [Today Entry] remains valid until that checkmark disappears?

Sheet 1

Sheet 2

Darren Mullen

@Tobiah

If you'll only ever have 1 matching Unit that also has the Today Entry box checked, then you can use the Collect() function to match the unit and that the Today Entry is check. The wrap the Collect() function with the Index and use an index value of 1.

=Index(Collect({Your criteria}), 1)

That will then only pull in the matching unit that also has the Today Entry box checked.

Tobiah

I tried this formula however whenever a specific unit met the matching criteria for both [Today Entry] and either [Tomorrow Entry] or [Yesterday Entry], the most recent entry that matches one of those columns takes precedence. For instance, Unit 1 met [Today Entry] criteria (using this formula:

=IFERROR(INDEX(COLLECT({Nursing Discharge Tracker_Today Entry Range}, {Nursing Discharge Tracker_Unit Range}, Unit@row), 1), "")

..but then later in the day another submission comes through which meets the [Tomorrow Entry] criteria which uses an identical formula that's adjusted for the appropriate columns), which eliminated the checkmark in the [Today Entry] column.

Is there not a way for these separate columns to be honored simultaneously?

Tobiah

To clarify, I have a [Today Entry] column in Sheet 1 and a [Today Entry] column in Sheet 2 and both sheets have a [Unit] column to match on. I want Sheet 2 to automatically add a checkmark to its [Today Entry] column when the [Today Entry] column in Sheet 1 is checked and to remain checked until the Sheet 1 [Today Entry] becomes unchecked (which depends on two helper columns using date- and time-dependent criteria).

The issue I run into is the moment an entry comes through Sheet 1 that no longer has the [Today Entry] column checked, it removes the checkmark from Sheet 2 also. Once again, I want [Today Entry] in Sheet 2 to remain checked if any [Today Entry] in Sheet 1 is still checked regardless of what new entries may be, matching on [Unit]. Is this possible?

@jmyzk_cloudsmart_jp, because you were helpful in getting the [Today Entry] to work for me, wondering if you have any thoughts!

Sheet 1

Sheet 2

jmyzk_cloudsmart_jp

Hi @Tobiah

I suspect the issue you're experiencing might be related to how Smartsheet handles time zones. When changes are made to a sheet via automation, cell links, or cross-sheet formulas, the entire sheet is recalculated in Pacific Time (PT).

https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones

This time zone override might be causing discrepancies in your [Today Entry] column, as the evaluations may not align with your local time when new rows are added or when the sheet recalculates dynamically.

Solution 1: Evaluate Dates Using Text
To avoid discrepancies caused by time zone recalculations, you could evaluate dates based on their displayed local time in the Created column. By treating the Created value as text, you can avoid reliance on Pacific Time and potentially ensure consistent evaluations based on your local settings.

The rows I created in the image below show 11/27/24 in the Created column. However, if I apply the DATEONLY function, the formula returns 11/26/24 as Japan is GMT+9.

So, I can use the TEXT functions like MID, LEFT, etc., to get the Date value that matches the local display value.

[DATEONLY Function Date] =DATEONLY(Created@row)
[Year] =2000 + VALUE(MID(Created@row, 7, 2))
[Month] =VALUE(MID(Created@row, 1, 2))
[Day] =VALUE(MID(Created@row, 4, 2))
[Text Based Date] =DATE(Year@row, Month@row, Day@row)

Solution 2: Adjust for Timezone Differences
Alternatively, if you are dealing with only one time zone, you can evaluate the date-related function's result using the Time zone difference. This re-evaluation is rather complex, so I prefer the first approach.

Let me know if you need further clarification or additional assistance!