software

I need some help with a spreadsheet that is setup as follows...

quot;SummaryofChangesquot; Sheet - Contains data organized by column headings
and each row represents a unique person. Some values can be identical
between rows, but the Social Security Number is column A will always be
unique. Column B contains either quot;new hirequot;, quot;existing employeequot;, or
quot;terminatedquot;. All other columns contain data about the employee.

The remaining sheets in the workbook are labelled according to quot;newquot;,
quot;existingquot;, and quot;termquot;.

quot;Newquot; Sheet - should list employee records that show quot;new hirequot;

quot;Existingquot; Sheet - should list employee records that show quot;existing
employeequot;

quot;Termquot; Sheet - should list employee records that show quot;terminatedquot;I need help getting this information to auto-populate on each of these
sheets. Although autofilter would normally be used is not a reasonable
option because of additions, subtractions and general formating changes
that will be done to each employee record once the are move to each
sheet.

Is there a way to do this without autofilter?? THANK YOU!--
TEAM
------------------------------------------------------------------------
TEAM's Profile: www.excelforum.com/member.php...oamp;userid=22810
View this thread: www.excelforum.com/showthread...hreadid=541860You could but why not just have a macro that does use autofilter for each
group and auto copies to the separate sheets, overwriting the old info each
time..

--
Don Guillett
SalesAid Software

quot;TEAMquot; gt; wrote in message
...
gt;
gt; I need some help with a spreadsheet that is setup as follows...
gt;
gt; quot;SummaryofChangesquot; Sheet - Contains data organized by column headings
gt; and each row represents a unique person. Some values can be identical
gt; between rows, but the Social Security Number is column A will always be
gt; unique. Column B contains either quot;new hirequot;, quot;existing employeequot;, or
gt; quot;terminatedquot;. All other columns contain data about the employee.
gt;
gt; The remaining sheets in the workbook are labelled according to quot;newquot;,
gt; quot;existingquot;, and quot;termquot;.
gt;
gt; quot;Newquot; Sheet - should list employee records that show quot;new hirequot;
gt;
gt; quot;Existingquot; Sheet - should list employee records that show quot;existing
gt; employeequot;
gt;
gt; quot;Termquot; Sheet - should list employee records that show quot;terminatedquot;
gt;
gt;
gt; I need help getting this information to auto-populate on each of these
gt; sheets. Although autofilter would normally be used is not a reasonable
gt; option because of additions, subtractions and general formating changes
gt; that will be done to each employee record once the are move to each
gt; sheet.
gt;
gt; Is there a way to do this without autofilter?? THANK YOU!
gt;
gt;
gt; --
gt; TEAM
gt; ------------------------------------------------------------------------
gt; TEAM's Profile:
gt; www.excelforum.com/member.php...oamp;userid=22810
gt; View this thread: www.excelforum.com/showthread...hreadid=541860
gt;
Here's one play which automates it using non-array formulas ..

A sample implementation is available at:
www.savefile.com/files/8936561
Auto-Extract Data to Own Sheet by Category.xls

In sheet: SummaryofChanges (the quot;masterquot; sheet)

Assume data in cols A to E, data in row2 down,
with the key col = col B (say quot;Categoriesquot;: New Hire, Existing Employee or
Terminated)

Using empty cols to the right,
List the 3 categories across in K1:M1

Put in K2: =IF($B2=K$1,ROW(),quot;quot;)
Copy K2 across to M2, fill down to say, M20
to cover the max expected extent of source data

Click Insert gt; Name gt; Define
Put under quot;Names in workbook:quot;: WSN
Put in the quot;Refers to:quot; box:
=MID(CELL(quot;Filenamequot;,INDIRECT(quot;A1quot;)),FIND(quot;]quot;,CELL(quot;Filenamequot;,INDIRECT(quot;A1quot;)
)) 1,32)
Click OK

(The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique taken from
a post by Harlan Grove.)

In a new sheet named: New Hire
With the same col headers pasted into A1:E1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(SummaryofChanges!$J:$J,,M ATCH(WSN,SummaryofChanges!$K$1:$M$1,0)),ROWS($A$1: A1))),quot;quot;,INDEX(SummaryofChanges!A:A,MATCH(SMALL(OF FSET(SummaryofChanges!$J:$J,,MATCH(WSN,SummaryofCh anges!$K$1:$M$1,0)),ROWS($A$1:A1)),OFFSET(Summaryo fChanges!$J:$J,,MATCH(WSN,SummaryofChanges!$K$1:$M $1,0)),0)))

Copy A2 across to E2, fill down to say, E10
(copy down by the smallest possible range sufficient
to cover the max expected extent for any category. Here, I've assumed that 9
rows (rows 2 to 10) is sufficient)

Cols A to E will return only the lines for cat: New Hire from
quot;SummaryofChangesquot;, with all lines neatly bunched at the top

Now just make a copy of the sheet: New Hire, rename it as the next cat:
Existing Employee, and we'd get the results for that category

Repeat the copy gt; rename sheet process to get the last of the 3 category
sheets (Terminated). Adapt to suit ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;TEAMquot; wrote:
gt;
gt; I need some help with a spreadsheet that is setup as follows...
gt;
gt; quot;SummaryofChangesquot; Sheet - Contains data organized by column headings
gt; and each row represents a unique person. Some values can be identical
gt; between rows, but the Social Security Number is column A will always be
gt; unique. Column B contains either quot;new hirequot;, quot;existing employeequot;, or
gt; quot;terminatedquot;. All other columns contain data about the employee.
gt;
gt; The remaining sheets in the workbook are labelled according to quot;newquot;,
gt; quot;existingquot;, and quot;termquot;.
gt;
gt; quot;Newquot; Sheet - should list employee records that show quot;new hirequot;
gt;
gt; quot;Existingquot; Sheet - should list employee records that show quot;existing
gt; employeequot;
gt;
gt; quot;Termquot; Sheet - should list employee records that show quot;terminatedquot;
gt;
gt;
gt; I need help getting this information to auto-populate on each of these
gt; sheets. Although autofilter would normally be used is not a reasonable
gt; option because of additions, subtractions and general formating changes
gt; that will be done to each employee record once the are move to each
gt; sheet.
gt;
gt; Is there a way to do this without autofilter?? THANK YOU!
gt;
gt;
gt; --
gt; TEAM
gt; ------------------------------------------------------------------------
gt; TEAM's Profile: www.excelforum.com/member.php...oamp;userid=22810
gt; View this thread: www.excelforum.com/showthread...hreadid=541860

Ensure that the names of the 3 sheets: New Hire, Existing Employee, Terminated
match exactly* with the 3 categories listed in col B in SummaryofChanges
*except for case

(Watch out for any inconsistencies, typos, extraneous white spaces, etc)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---

gt; A sample implementation is available at:
gt; www.savefile.com/files/8936561
gt; Auto-Extract Data to Own Sheet by Category.xls

Have corrected some discrepancies in the earlier sample file
(just detected, apologies). Revised sample uploaded at the same link.
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---