WORKING WITH MULTIPLE WORKSHEETS

J

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.
 
L

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 i
the same row on each sheet:

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

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

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

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

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

Dean Thoma
 
L

Lost4Now

OOPS!

Forgot something!

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

Dean Thoma
 
Top