OK, this is what I am working with! It’s a very complicated workbook, and I have multiple (23) sheets in my work book.
This workbook is for Military Aviation Airframe inspection schedules. (Yes, I’m military)
Narratives aka names of inspections are listed in my table TABLE_18[NARR]
Airframe hour when inspection in narrative is due TABLE_18[DUE]
Sheet Table to display all information on ACFT_STATUS
Aircraft Serial number on ACFT_Status sheet ACFT_STATUS[TAIL]
Aircraft serial number (Last 3 of the "tail" ex.. 789) [EI_SN]
PMI = PHASE MAINTENANCE INSPECTION
The below formula is placed in ACFT_STATUS Table in a column named [PMI DUE]
(Sorry if too much information, I like to understand what the information means when i work with formulas.)
This current formula compares Black Hawk UH60L model Helicopter, two major inspections occur at different intervals, I’m looking for the one [due] first aka "MIN"
Everything in my workbook is an array, there are thousands of entries for multiple aircraft, so I have to match the aircraft [EI_SN] to the inspection to ensure
I have the correct aircraft [EI-SN] and then the correct inspection in this case [PMI-1] or [PMI-2]. I use connections to query my database and placed into TABLE_18.
=IF(INDEX(TABLE_18[DUE],MATCH("PMI-1", IF(TABLE_18[EI_SN]=ACFT_STATUS[[#THISROW],[TAIL]],TABLE_18[NARR]),0))
<INDEX(TABLE_18[DUE],MATCH("PMI-2",IF(TABLE_18[EI_SN]=ACFT_STATUS[[THISROW],[TAIL]],TABLE_18[NARR]),0)),
INDEX(TABLE_18[DUE],MATCH("PMI-1",IF(TABLE_18[EI_SN]=ACFT_STATUS[[THISROW],[TAIL]],TABLE_18[NARR]),0)),
INDEX(TABLE_18[DUE],MATCH("PMI-2",IF(TABLE_18[EI_SN]=ACFT_STATUS[[THISROW],[TAIL]],TABLE_18[NARR]),0))
THIS FORMULA IS RIDICULOUS! I’m sure there is a simpler way to write it, however this is what worked for me.
So, if you are still with me - This has worked for my needs for months, However I now have UH60M model aircraft as well and I need to rewrite my
Formula, So I now need to compare two different model aircraft UH60L and UH60M model aircraft. The UH60M model aircraft inspection are the same, however
the inspections [NARR] are not worded the same.
These inspections are called "Perform 720 Hour PMI-1" and "Perform 720 Hour PMI-2", the kicker is that I need this ONE formula to look for all 4
Inspections that match aircraft [EI_SN] and give me the closest [DUE] located in TABLE_18.
The 4 inspections are Named
"PMI-1" "PMI-2" compare give MIN if they exist in table_18
"Perform 720 Hour PMI-1" "Perform 720 Hour PMI-2", compare give MIN if they exist in table_18
[DUE] is number
[NARR] is text
[EI_SN] is number
[TAIL] is number
I know this formula does not work and is not even a formula but to clarify:
=index(table_18[due],Match("PMI-1","PMI-2,"Perform 720 Hour PMI-1", "Perform 720 Hour PMI-2") if(ACFT_STATUS[TAIL]=TABLE_18[EI_SN] return [DUE] MIN
To keep from getting more complicated that I have already, when this formula "runs" there will only be two of these inspections available in this table available at any
given time. If you want a explanation for this, ask I will explain. Placing this formula in another worksheet or workbook is NOT an option.
I have worked for days, tried MIN, IFERROR, and tried a ton of IF statements, then Google'd the Internet - reached the END of the internet, and came here to ask for help.
Also, my workbook is "Sensitive" so only sample information is contained in the Cleaned Workbook posted below.
Thanks for reading my Novel
Bookmarks