Find the latest cell that I have value, for a given name, which will equal another cell.

[M1225]

Hi guys,

I am pretty sure there is multiple functions to this but I can't get the proper formula.

I have tWo sheet.

Important data Sheet 1:
TABLE1[DATE] : TABLE1[PIC] : TABLE1[SIC] : TABLE1[NUMBER OF LANDINGS] : TABLE1[NUMBER OF LANDINGS3]

1. TABLE1[DATE] is -12 column from PIC, and -13 from SIC
2. Depending of the flight, the PIC or the SIC will have completed either NUMBER OF LANDINGS or NUMBER OF LANDINGS3. In those two colums usually the number is - BLANK or 1,2,3...-

Important data sheet 2:
1. A5 : Pilot name, which will appear either in PIC or SIC of TABLE 1

What formula I am trying to do in my cell:
Give me the date of TABLE 1[DATE] of the last time the A5 pilot as appeared in PIC or SIC **AND** that represent the date which signify the pilots A5 have completed 3 landings (total of NUMBER OF LANDINGS and NUMBER OF LANDINGS3).

To keep currency, pilots have to complete 3 landings within 90 days. By giving me that date that signify 3 landings have been completed, I can have a conditional formatting that will turn the cell in red when it's getting close to the 90 days from today.

Does that make sense ?

Thank you so much.

 

[PhilS2520]

Sorry M1225,

This does not give us a clear picture as to what you are trying to do.

Would it be possible to write it all I English and not what I call Xlies. That is say I want to copy a cell that has... vs. In cell where I have PIC... We do not know what PIC is, but we all know what a cell is and how to copy it.

Think of telling us a story, and not telling us what happened. You know the details and we know the technical so we need to merge these together to get you the right answer.

Hope this helps.

 

[M1225]

Thank you Phil for this information. It was really clear in my head but obviously not for someone that don't have my workbook.

Let's try that again:

I have one workbook, with two sheets.
- Sheet #2 has a column of names. I am looking for a formula that will be looking for data related to that name on Sheet#1. Let's take per example name A5.
- Name A5 is a pilot. His name is Bobby. For each flight completed within the company, I fill Sheet#1, as below. (This is a small view ; my original workbook has just below 700 flights)


[TABLE="width: 100"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]TABLE 1[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[/TR]
[TR]
[TD="align: center"]
[/TD]
[TD="align: center"][Date][/TD]
[TD="align: center"][PIC][/TD]
[TD="align: center"][SIC][/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][Number of Landings][/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][Number of Landings3][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATE[/TD]
[TD]PILOT#1[/TD]
[TD]PILOT#2 [/TD]
[TD]# of takeoff PILOT#1[/TD]
[TD]# of landing PILOT#1[/TD]
[TD]# of takeoff PILOT#2 [/TD]
[TD]# of landing PILOT#2 [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]18/01/01[/TD]
[TD]Bobby[/TD]
[TD]Carole[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]18/01/02[/TD]
[TD]Bobby[/TD]
[TD]Carole[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]18/01/03[/TD]
[TD]George[/TD]
[TD]Bobby[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]18/01/04[/TD]
[TD]Matt[/TD]
[TD]George[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The final data I want to collect is the date , of 3 landings ago. If it helps understand better, the pilot has to make at least three landings within the preceding 90 days. So for me finding that date, I will know when and if the pilot has exceeded the 90 days.

So I already made a formula that gives me the number of landings for the last 90 days for my A5 pilot, but I have difficulty knowing how to extract the date of 3 landings ago, which I have tried with a mix of FIND, MAX, OFFSET, COUNTIF ??

(Below is the formula I used for the amount of landings of the past 90 days. Reference in RED in Table 1 above, where A1 =TODAY[] )

=SUMIFS((Table1[Number of Landings]),(Table1[Date]), ">="&($A$1-90), Table1[PIC], A5)+SUMIFS((Table1[Number of Landings]),(Table1[Date]), ">="&($A$1-90), Table1[SIC], A5)+SUMIFS((Table1[Number of Landings3]),(Table1[Date]), ">="&($A$1-90), Table1[PIC], A5)+SUMIFS((Table1[Number of Landings3]),(Table1[Date]), ">="&($A$1-90), Table1[SIC], A5)


Thank you!!!!!