Is there a way to set up a formula to run only once, or to not run based on some critieria?

Brett Tillman

I am using an INDEX/COLLECT formula to harvest cell(s) data from another sheet.

=INDEX(COLLECT({Beta Test-Pilot Review Boards | Board Date}, {Beta Test-Pilot Review Boards | Employee Number}, [Trainee Employee Number]@row), 1)

After much work, the formula works as intended, and I've even tested it successfully with an IFERROR statement also. My issue is that both source and target sheets could possible have repeat customers, where the criteria I'm using to identify the row will be the same (Employee Number), even though each row/instance for this employee on the sheets is unique and has different data in the fields I am trying to harvest. Does this question make sense? I was wondering if a formula could be set to run only once, or if a criteria statement associated with the entry date column could be used in some syntax to differentiate the rows (like only bring back the data if the row's entry date column is on/after the date the formula is triggered to run). As written above, my formula works flawlessly, but will overwrite any older row's entry for the same employee with the latest cell data, and not preserve the previous row data. I know I could probably set automation to remove closed/old rows to another sheet, but I prefer not to do this.

BullandKhmer

Do you have date related data on both source and destination sheets? Could you not add another condition based on these? i.e.

=INDEX(COLLECT({Beta Test-Pilot Review Boards | Board Date}, {Beta Test-Pilot Review Boards | Employee Number}, [Trainee Employee Number]@row,{Date Reference},[Date reference]@row), 1)

Brett Tillman

That is exactly my question. But since the sheets are maintained by different groups and accessed at different times, the date column I would need to reference would not be absolutely predictable and not necessarily match in both sheets. If there is a way to enter DATE as a criteria, but with a syntax that specified "latest" or "most recent" date, or even an "on/after" acceptable date range, that is what I'm looking for. I just can't figure out if such a request exists. I have seen a DATE MAX syntax, but don't understand exactly what that means or how it works. My only other option is to move older "closed" rows in my target sheet to a "closed" sheet, so that the data returned to the active sheet will not corrupt the older rows. I don't want to create another sheet but that may be my only option.