Conditional formatting referencing another sheet and returning multiple column items

What I'd like to do is pull info from one sheet into another.

The first sheet has a table, 5 columns wide and 250 rows (A5:D255). The fifth column has text from a 3-item pull down list. I would like to, on the second sheet, pull all the rows of info which have one of those three items labeling it. For instance, some have the label "Dog". I want to have a second sheet that pulls all the rows of info that have the label "Dog" from the first page.

The sample file shows how I want the second (and other) sheets to look like. I want to reference the first sheet, cell E2 as the index and if it says "Dog", then pull the info from columns A2:D2 into a sheet labeled Dog.

This file will be updated somewhat regularly, with new items added into the full list in the middle, so I'm trying to figure out an easier way to manage the other sheets that break down that list into categories than having to update those individually.

Right now I have the first page set up will the full list and the pull down menu items labeling each row, but I wasn't sure how to go about writing a formula for the other pages.

here is one way:

In F2 of All Items sheet enter helper formula:

=E2&"_"&COUNTIF(E$2:E2,E2)

copied down

In F1 of the individual sheets put in the key corresponding keyword from column E of All Items.

Then select all the sheets except the All Items sheet and in G1 enter:

=COUNTIF('All items'!E:E,F1)

and in A2 enter:

=IF(ROWS($A$1:$A1)>$G$1,"",INDEX('All items'!A:A,MATCH($F$1&"_"&ROWS($A$1:$A1),'All items'!$F:$F,0)))

copied down as far as you want and across to capture all columns.

Then ungroup the sheets.

Hi Bundelodaisies,

Another way would be to do advanced filters from your first sheet onto the three other sheets. After doing this and attaching the file it became clear most of these ways are too hard.

What you should really do is to use a single sheet with an AutoFilter. Click anywhere in your data and then on the Data Tab. From there make the data a table and a dropdown will appear. Click the Item and click on the Dog, Cat or Squirrel that you want to leave in the filter.

See http://www.contextures.com/xlautofilter01.html