Help required with FORMULA/FORMULATION

[Nova1979]

Can anyone help witth a FORMULA.

I need to transfer data from INSPECTION

To Reference

Placing a "Y" in column E in INSPECTION will bring across

Discription, P/N and Qty Req into the Reference Sheet​

Placing a "Y" in column A in INSPECTION will bring across Serail, Growth Work Descritption, Listing and HRS into the Reference Sheet.

Some time ago I received help and have been trying to utilise the following formula

=IFERROR(INDEX(INSPECTION!$C$15:$C$429,AGGREGATE(15,6,(ROW(INSPECTION!$E$15:$E$429)-MIN(ROW(INSPECTION!$E$15:$E$429))+1)/(INSPECTiiION!$E$15:$E$429="Y"),ROWS(F$1:F8))),"")

This transferes the information well (currently being used on other sheets I have).

The problem I am having with the formula and the current needs is if I need to include the item in C14 INSPECTION, I need to enter a "Y" in E14 INSPECTION. I then need a row added in the Reference Sheet against Serial 66 (currently rows 5:10 Reference Sheet) and the information inserted into row 11 (P/N, Description, Qty Req). This will move Serial 12 down to ro 12 instead of row 11 as seen in image

If I place a "Y" in A17 INSPECTION with the relevant information (Serial, Growth Work, Listing HRS). I am happy to have this added to the bottom of the list, utilising the above formula. Than I have the above issue to contend with
of a possible growing parts list.

I am trying to come up with solutions to spreadsheets I have no control over with the design.
Hopefully this is clear to anyone that could help
Is this possible to do?

 

[Nova1979]

I have the columns A:E working properly in the Reference sheet using the above formula, now I am after an assist with getting the right parts listing against the right serials
For example if I select any parts listed against serial 12 in INSPECTION, they only list against serial 12 in Reference

Again if anyone may be able to shed some light, it would be greatly appreciated

Thanks

 

[Nova1979]

BUMP