looping and filtering without Macro

[purpleozzie]

Hi guys,

In my spreadsheet there is a sheet called 'pre-packaged beer' which has a list of all beer we produce.

We produce some of our beers in keg, some in cask, and some in cans

I've used this formula on another sheet that pulls the list of beers in three times.. Once for Keg once for Cask and once for Cans...

=IF(ROW()<=COUNTA('Pre-Packaged Beer'!A:A)*3+4,INDEX('Pre-Packaged Beer'!A:A,MOD(ROW()-5,COUNTA('Pre-Packaged Beer'!A:A))+2),"")

On 'pre-packaged beer' sheet there are three columns O(Keg), P(Cask), Q(Cans) which have a Y in if we produce that product or N if we don't... What I want is for this formula to go down the list checking column O and if there's a 'N' skipping this line of data but not leaving a blank line if that all makes sense..

I also don't want to use Macros

 

[Fazza]

Sounds like a query might suit. Doesn't use macros or formulas. It is like a database query & refreshable like a pivot table. Suggest you refer Excel help or google.

 

[Peter_SSs]

I've done this on a single sheet, but is this the sort of thing you are after?
Formula in S2 is copied across and down.

Excel Workbook
	A	O	P	Q	R	S	T	U
1	Type	Keg	Cask	Cans		Keg	Cask	Cans
2	Beer 1	Y	N	N		Beer 1	Beer 2	Beer 2
3	Beer 2	N	Y	Y		Beer 3	Beer 4	Beer 3
4	Beer 3	Y	N	Y		Beer 7	Beer 5	Beer 8
5	Beer 4	N	Y	N			Beer 6	Beer 9
6	Beer 5	N	Y	N			Beer 7
7	Beer 6	N	Y	N			Beer 8
8	Beer 7	Y	Y	N
9	Beer 8	N	Y	Y
10	Beer 9	N	N	Y
11
Beers