Return 1 of 3 values based on search results in Calc

I have a Calc workbook with sheets named Project n, where n is the project number as its own individual sheet. Formatting:

A	B	C	D	E	F	G	H
Date	String	String	String	String	String	String	Event1
Date	String	String	String	String	String	String	Event2

The workbook has a summary sheet to track events in all project sheets. Formatting:

	Event1	Event2
Project 1	Y/N	Y/N
Project 2	Y/N	Y/N

The Y/N cells look up the Event for their respective Project to check if the event exists in the project sheet with:

=IF(COUNTIFS(INDIRECT(CONCATENATE("$",$A2,".$H$1:$H$250")),B$1),"Y","N")

The INDIRECT(CONCATENATE()) part takes the name of the project (in this case, $A2 references Project 1) to reference the sheet that goes by the same name, then searches that sheet's H column for any cell matching the event name (in this case, B$1 references Event1).

I wish to change the formula so it returns C for completed events before TODAY(), I for incomplete events that don't exist in the data, and F for events that do exist in the data but are after TODAY(). The project sheets contain date values in the A columns.

However there may be duplicate events on different dates. I need to ensure that in the case of duplicate events, a F Future status is returned if the latest date of an event number is in the future.

Essentially, I'm trying to do this:

If Eventn exists in ProjectSheetColumnH(event) and the highest ProjectSheetColumnA(date) is less than TODAY(), then "C"
ElseIf Eventn exists in ProjectSheetColumnH(event) and the highest ProjectSheetColumnA(date) is greater than TODAY(), then "F"
Else "I"

This is well and truly into database territory, but it's not something I'm able to use, no matter how simple of a matter it would be to do this in firebird. Stuck with spreadsheets, unfortunately. Where do I go from here?

Solution

Get the most recent date for a particular event by using MAXIFS. Something like this:

=MAXIFS(INDIRECT(A2&".A2:A6");INDIRECT(A2&".H2:H6");"="&B1)<TODAY()

This gets all of the dates where the row matches a particular event name and then returns the highest of those dates.

Your actual formula would need to include two or three of these MAXIFS statements — three if you decide to use it instead of COUNTIFS for the "Else" part.

The LET function is scheduled for LibreOffice 2024.8, making it possible to assign to a variable. That way, only one MAXIFS should be needed in each formula, and then the variable can be used three times. (The CURRENT function can't handle this correctly and only has a few specialized uses).