Formula help

[Nova1979]

I am great full for all the help so far. Now I have another issue which is beyond my understanding. I was given a formula
=IF(INDIRECT("Sheet1!E"&ROW(A15))="y",INDIRECT("Sheet1!F"&ROW(A15)),0)
This is located in a secondary sheet and references a master sheet. It allows me to add a row on the master and have the correlating data in the second sheet move accordingly.
My problem now is the inability to remove excess blank rows on the secondary sheet
Example. I place a “Y” in the relative master cells E14, E15, E17. This is then referenced on the second sheet in rows 14, 15, 17. Now I need to shorten the second sheet for printing by removing all blank rows (in this case being row 16). I am dealing with over 1000 lines of data which is why I am after help
Unfortunately due to the data involved I am unable to currently supply a sample

Can someone help.

Thanks once again

 

[FormR]

Hi, something like this maybe.

Book1
	A
1	Result
2	ONE
3	TWO
4	FOUR
5	SIX
6	NINE
Sheet2

Cell Formulas
Range	Formula
A2:A6	A2	=IFERROR(INDEX(Sheet1!$F$14:$F$1000,AGGREGATE(15,6,(ROW(Sheet1!$E$14:$E$1000)-MIN(ROW(Sheet1!$E$14:$E$1000))+1)/(Sheet1!$E$14:$E$1000="Y"),ROWS(A$2:A2))),"")

Book1
	E	F
14	Y	ONE
15	Y	TWO
16		THREE
17	Y	FOUR
18		FIVE
19	Y	SIX
20		SEVEN
21		EIGHT
22	Y	NINE
Sheet1

 

[Nova1979]

Ok. I am relatively new to formula. As I mentioned someone helped with the formula I listed. I can grasp that works however yours is far beyond me. Could you possibly explain how it works.

 

[FormR]

Could you possibly explain how it works.

Hi, firstly, does it work for you?

 

[Nova1979]

After a quick base trial, it looks like it works how I need without the need to delete rows in the secondary reference sheet whilst allowing me to insert rows into the master sheet.
will it work on merged cells?

 

[FormR]

will it work on merged cells?

You could try it, but in general, it's a good idea to avoid using merged cells.

 

[Nova1979]

Thank so much for your help. The formula you provided (though I have no idea how it works as it’s far beyond my understanding) works perfectly.
I have even had success incorporating it with the merged cells.
Thanks again