Excel 2016 - wish to pull line of data from Sheet 1 to Sheet 2 where condition is met, without blank rows

[Gary57]

Thanks in advance for considering my plea.
I am using Excel 2016 and am trying pull lines of data from a table of data on one sheet (Input Screen)

and pulling it across to a second sheet (Summary) but without showing any blank rows where there are no entries on the first sheet.

As you can from Line 14 the Summary tab, Line 4 of the Input Screen tab doesn't come across as it has a nil value. This is the result I want, but I also want to group all results without blank rows. I wanted to use the FILTER formula but this isn't supported by Excel 2016.
I am using formula =IF('Input Screen'!B4>0,'Input Screen'!A4,"") currently to pull across the data.
Can anyone suggest a suitable formula that would bring across to the Summary tab the data in columns A to D where the value in Column B is >0, but preventing blank rows being inserted where the value is zero? Appreciate your knowledgeable assistance.

 

[etaf]

=IFERROR(INDEX(A$2:A$12,SMALL(IF($B$2:$B$12=1,ROW($B$2:$B$12)),ROW(1:1))-1,1),"")

Book1
	A	B	C	D
1
2	desc	qty	rate	cost
3	desc-a1	1	x	100
4	desc-a2	0	y	2000
5	desc-a3	1	z	300000
6
7
8
9
10
11
12	desc	qty	rate	cost
13	desc-a1	1	x	100
14	desc-a3	1	z	300000
Sheet1

Cell Formulas
Range	Formula
B12:D12	B12	=B2
A13:D14	A13	=IFERROR(INDEX(A$2:A$12,SMALL(IF($B$2:$B$12=1,ROW($B$2:$B$12)),ROW(1:1))-1,1),"")

 

[Peter_SSs]

Would that post #2 suggestion require Ctrl+Shift+Enter confirmation in Excel 2016?
I also note that if any new rows are subsequently added at the top of the worksheet containing the formula, the formula will return incorrect results.

I am using Excel 2016

Please add that information to your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

The suggestion below is somewhat similar to thee previous one but definitely does not require C+S+E confirmation and would still return correct results if new rows are subsequently added at the top.
Where I have ranges like 'Input Screen'!A$3:A$100 in the formula, make sure the sheet name is correct for you and that the range starts at the first row below the headings in the input sheet and the last row (where I have $100) is large enough to cover any amount of data you might have.
Where I have ROWS(A$12:A12) in the top left formula, make sure that cell reference is the cell that the first formula is in.

Gary57.xlsm
	A	B	C	D
1
2	desc	qty	rate	cost
3	desc-a1	1	x	100
4	desc-a2	0	y	2000
5	desc-a3	1	z	300000
6
Input Screen

Gary57.xlsm
	A	B	C	D
12	desc	qty	rate	cost
13	desc-a1	1	x	100
14	desc-a3	1	z	300000
15
16
Summary

Cell Formulas
Range	Formula
A13:D16	A13	=IFERROR(INDEX('Input Screen'!A:A,AGGREGATE(15,6,ROW('Input Screen'!A$3:A$100)/('Input Screen'!$B$3:$B$100>0),ROWS(A$12:A12))),"")

 

[Gary57]

Would that post #2 suggestion require Ctrl+Shift+Enter confirmation in Excel 2016?
I also note that if any new rows are subsequently added at the top of the worksheet containing the formula, the formula will return incorrect results.


Please add that information to your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

The suggestion below is somewhat similar to thee previous one but definitely does not require C+S+E confirmation and would still return correct results if new rows are subsequently added at the top.
Where I have ranges like 'Input Screen'!A$3:A$100 in the formula, make sure the sheet name is correct for you and that the range starts at the first row below the headings in the input sheet and the last row (where I have $100) is large enough to cover any amount of data you might have.
Where I have ROWS(A$12:A12) in the top left formula, make sure that cell reference is the cell that the first formula is in.

Gary57.xlsm
	A	B	C	D
1
2	desc	qty	rate	cost
3	desc-a1	1	x	100
4	desc-a2	0	y	2000
5	desc-a3	1	z	300000
6
Input Screen

Gary57.xlsm
	A	B	C	D
12	desc	qty	rate	cost
13	desc-a1	1	x	100
14	desc-a3	1	z	300000
15
16
Summary

Cell Formulas
Range	Formula
A13:D16	A13	=IFERROR(INDEX('Input Screen'!A:A,AGGREGATE(15,6,ROW('Input Screen'!A$3:A$100)/('Input Screen'!$B$3:$B$100>0),ROWS(A$12:A12))),"")

=IFERROR(INDEX(A$2:A$12,SMALL(IF($B$2:$B$12=1,ROW($B$2:$B$12)),ROW(1:1))-1,1),"")

Book1
	A	B	C	D
1
2	desc	qty	rate	cost
3	desc-a1	1	x	100
4	desc-a2	0	y	2000
5	desc-a3	1	z	300000
6
7
8
9
10
11
12	desc	qty	rate	cost
13	desc-a1	1	x	100
14	desc-a3	1	z	300000
Sheet1

Cell Formulas
Range	Formula
B12:D12	B12	=B2
A13:D14	A13	=IFERROR(INDEX(A$2:A$12,SMALL(IF($B$2:$B$12=1,ROW($B$2:$B$12)),ROW(1:1))-1,1),"")

Thanks Etaf. I tried your suggestion but was unable to get it to work. I suspect, as suggested below, that I need to Control Shift Enter, so will try it

 

[Gary57]

Would that post #2 suggestion require Ctrl+Shift+Enter confirmation in Excel 2016?
I also note that if any new rows are subsequently added at the top of the worksheet containing the formula, the formula will return incorrect results.


Please add that information to your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

The suggestion below is somewhat similar to thee previous one but definitely does not require C+S+E confirmation and would still return correct results if new rows are subsequently added at the top.
Where I have ranges like 'Input Screen'!A$3:A$100 in the formula, make sure the sheet name is correct for you and that the range starts at the first row below the headings in the input sheet and the last row (where I have $100) is large enough to cover any amount of data you might have.
Where I have ROWS(A$12:A12) in the top left formula, make sure that cell reference is the cell that the first formula is in.

Gary57.xlsm
	A	B	C	D
1
2	desc	qty	rate	cost
3	desc-a1	1	x	100
4	desc-a2	0	y	2000
5	desc-a3	1	z	300000
6
Input Screen

Gary57.xlsm
	A	B	C	D
12	desc	qty	rate	cost
13	desc-a1	1	x	100
14	desc-a3	1	z	300000
15
16
Summary

Cell Formulas
Range	Formula
A13:D16	A13	=IFERROR(INDEX('Input Screen'!A:A,AGGREGATE(15,6,ROW('Input Screen'!A$3:A$100)/('Input Screen'!$B$3:$B$100>0),ROWS(A$12:A12))),"")

Thanks Peter_SSs. I will try your solution this afternoon and report back. really grateful for the member support here.

 

[Gary57]

Would that post #2 suggestion require Ctrl+Shift+Enter confirmation in Excel 2016?
I also note that if any new rows are subsequently added at the top of the worksheet containing the formula, the formula will return incorrect results.


Please add that information to your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

The suggestion below is somewhat similar to thee previous one but definitely does not require C+S+E confirmation and would still return correct results if new rows are subsequently added at the top.
Where I have ranges like 'Input Screen'!A$3:A$100 in the formula, make sure the sheet name is correct for you and that the range starts at the first row below the headings in the input sheet and the last row (where I have $100) is large enough to cover any amount of data you might have.
Where I have ROWS(A$12:A12) in the top left formula, make sure that cell reference is the cell that the first formula is in.

Gary57.xlsm
	A	B	C	D
1
2	desc	qty	rate	cost
3	desc-a1	1	x	100
4	desc-a2	0	y	2000
5	desc-a3	1	z	300000
6
Input Screen

Gary57.xlsm
	A	B	C	D
12	desc	qty	rate	cost
13	desc-a1	1	x	100
14	desc-a3	1	z	300000
15
16
Summary

Cell Formulas
Range	Formula
A13:D16	A13	=IFERROR(INDEX('Input Screen'!A:A,AGGREGATE(15,6,ROW('Input Screen'!A$3:A$100)/('Input Screen'!$B$3:$B$100>0),ROWS(A$12:A12))),"")

Thanks. Worked a treat once I had set up the correct source cells

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and for updating your version details.