WORKING WITH MULTIPLE WORKSHEETS

[JC White]

I am in the NC National Guard, and I am working on some spreadsheets to make
filing easier. I have one worksheet called "Alpha Roster" that I have listed
all the Soldiers within my Unit. This worksheet list the Soldiers each on a
separate row, and I have various data listed in the columns--last name, first
name, MI, SSN, Location, ect.

I would like to be able to pull all the data in a row off this worksheet
into another if certain criteria is met within one column.

For example, I have three locations--Kinston, Wallace, and Beulaville. I
would like to have all the rows having Kinston in column "M" pulled to the
second spreadsheet I'll call DET1. The same applies for the other locations
on different spreadsheets called DET2 and Echo.

Please help. How do I get these rows of information to be displayed on the
second and third worksheets? This will allow me to have updates made only on
one sheet, but view data on various sheets based on what I need to accomplish.

Thank you all for your time and assistance.

[Norman Jones]

Hi JC,

See Debra Dalgleish's tutorial on the Advanced Filter feature at:

http://www.contextures.com/xladvfilter01.html

See particularly the section entitled: 'Extract Data to Another Worksheet'


---
Regards,
Norman


"JC White" <JC [email protected]> wrote in message
news:[email protected]...
>I am in the NC National Guard, and I am working on some spreadsheets to
>make
> filing easier. I have one worksheet called "Alpha Roster" that I have
> listed
> all the Soldiers within my Unit. This worksheet list the Soldiers each on
> a
> separate row, and I have various data listed in the columns--last name,
> first
> name, MI, SSN, Location, ect.
>
> I would like to be able to pull all the data in a row off this worksheet
> into another if certain criteria is met within one column.
>
> For example, I have three locations--Kinston, Wallace, and Beulaville. I
> would like to have all the rows having Kinston in column "M" pulled to the
> second spreadsheet I'll call DET1. The same applies for the other
> locations
> on different spreadsheets called DET2 and Echo.
>
> Please help. How do I get these rows of information to be displayed on the
> second and third worksheets? This will allow me to have updates made only
> on
> one sheet, but view data on various sheets based on what I need to
> accomplish.
>
> Thank you all for your time and assistance.

[Lost4Now]

The following formula has worked for me - good luck!

=IF(Sheet1!$B4=1,Sheet1!A4,"")

Assuming that the column headings are the same on each sheet and are in the same row on each sheet:

Substitute "AlphaRoster" for Sheet1 - the criteria column and criteria for B4=1

Enter the formula into the first cell below the columnA heading in each worksheet and then copy it into the each column. Then copy that row into the as many rows as you are going to need to use - more if you are going to be adding more enteries.

Each sheet should display only the matches from your criteria, and blank rows for the rows that do not match. Then "Data-Filter" each worksheet to filter out the blank rows.

Each time you change the criteria on the AlphaRoster you will need to re-filter the affected worksheet. Small price to pay.

I'm not an excel guru - far from it. Butcher is more fitting - but it works for me. I do hope it helps you until someone shows us the proper way.

Dean Thomas

[Lost4Now]

OOPS!

Forgot something!

If any cells are blank in the AlphaRoster sheet, they will appear as "0" on the filtered sheets. You can clear that with Format/Cells/Number/Custom and entering a "#" in the Type input box. Apply that formatting for all the cells in the data range - you will have to clear the "Filter" first.

Dean Thomas