How to Filter without using Filter!

[IanBr]

Bit of an odd one...
I have a table of data containing info for multiple branches... I want to be able to hide the main data but have a separate tab where if a single cell is populated with the branch name, a new table will populate only showing the lines relevant to that branch.... essentially filtering it to only that branch's data but without using filter

So essentially as per the 2 screenshots but in the "almost outcome" one, I don't want the "0" lines to appear

Any thoughts?

 

[Peter_SSs]

With your version of Excel, you could try this (latest versions have an easier way)

IanBr.xlsm
	A	B	C	D
1	Branch	Name	Data 1	Data 2
2	1	A	z	9
3	2	B	y	8
4	3	C	x	7
5	1	D	w	6
6	2	E	v	5
7	3	F	u	4
8	3	G	t	3
9	1	H	s	2
10	2	I	r	1
Sheet1

IanBr.xlsm
	A	B	C
1	Branch	1
2
3	Name	Data 1	Data 2
4	A	z	9
5	D	w	6
6	H	s	2
7
8
Sheet2

Cell Formulas
Range	Formula
A4:C8	A4	=IFERROR(INDEX(Sheet1!B:B,AGGREGATE(15,6,ROW(Sheet1!B$2:B$100)/(Sheet1!$A$2:$A$100=$B$1),ROWS(A$4:A4))),"")