Summarizing specific data from one worksheet on another

[Seth]

Hello,
I have a fairly simple spreadsheet of population data for a mouse colony.
The layout is simplified as follows:
Column A: Cage number (0001, 0002, etc)
Column B: Females of strain A
Column C: Females of strain B
Column D: Males of strain A
Column E: Males of strain B

There is a bit more information being tracked, and many more strains, but
this is essentially the layout. I am trying to create a second worksheet that
will list information from the main census sheet, but only for breeding
cages. This would be any cage with at least one male and one female. I took
a straightforward approach
=IF(AND(Sum(B2:C2)>1,Sum(D2:E2)>1),A1)

In terms of presentation, however, this leaves a large number of blank rows,
and makes finding active breeding cages in a large colony difficult. Is there
a way to design a new worksheet that will only contain information from rows
that have a breeding cage?

Thank you

 

[Max]

Think if you use something like this in a helper col to the right,
say, in K2, copied down:
=IF(AND(SUM(B2:C2)>1,SUM(D2:E2)>1),"x","")
you could then easily apply autofilter* on col K,
and choose: x from the droplist
to get all the required results (all the blue filtered rows)

*Select col K, then click Data>Filter>Autofilter
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,000 Files:354 Subscribers:53
xdemechanik