WORKING WITH MULTIPLE WORKSHEETS
 

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.

WORKING WITH MULTIPLE WORKSHEETS
 

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 wrote in message
...
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.

WORKING WITH MULTIPLE WORKSHEETS
 

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
------------------------------------------------------------------------
Lost4Now's Profile: http://www.excelforum.com/member.php...o&userid=29345
View this thread: http://www.excelforum.com/showthread...hreadid=495964

WORKING WITH MULTIPLE WORKSHEETS
 

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


--
Lost4Now
------------------------------------------------------------------------
Lost4Now's Profile: http://www.excelforum.com/member.php...o&userid=29345
View this thread: http://www.excelforum.com/showthread...hreadid=495964