ExcelBanter - Multiple Worksheet

Here's one play which automates it using non-array formulas ..

A sample construct is available at:
http://www.savefile.com/files/6028021
AutoCopyData_ByInitialsCol_To_RespNameSheet_NonArr ay.xls

In sheet: All (the "master")

Assume data in cols A to E, data in row2 down,
with the key col = col D ("Initials"),
whe S, M, K, P, etc would be listed

List the initials across in K1:N1 (S, M, P, K)

Put in K2: =IF($D2=K$1,ROW(),"")
Copy across to N2, fill down to say, N20
to cover the max expected extent of data

Click Insert Name Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+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 came from
a post by Harlan.)

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

Put in A2:
=IF(ISERROR(SMALL(OFFSET(All!$J:$J,,MATCH(LEFT(WSN ,1),All!$K$1:$N$1,0)),ROWS
($A$1:A1))),"",INDEX(All!A:A,MATCH(SMALL(OFFSET(Al l!$J:$J,,MATCH(LEFT(WSN,1)
,All!$K$1:$N$1,0)),ROWS($A$1:A1)),OFFSET(All!$J:$J ,,MATCH(LEFT(WSN,1),All!$K
$1:$N$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 initial.
Here, I've assumed that 9 rows (rows 2 to 10) is sufficient)

Cols A to E will return only the lines for the initial: S from "All",
with all lines neatly bunched at the top

Now, just make a copy of the sheet: Scott, rename it as: Mark
and we'd get the results for initial: M

Repeat the copy rename sheet process
to get the rest of the initials sheets (a one-time job)

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"bonzai18" wrote in
message ...

I'm not sure how to explain this, but here we go.

I have a workbook w/ multiple worksheets. The first worksheet is
titled ALL. This includes all information from other worksheets. How
can I have certain information transfer to other worksheets by
recognizing initials?

Basically if the "All" worksheet has has an S listed it - can I
transfer to worksheet titled Scott w/o the beating of copy and paste?

Thanks in advance.

--
bonzai18
------------------------------------------------------------------------
bonzai18's Profile:
http://www.excelforum.com/member.php...o&userid=32448
View this thread: http://www.excelforum.com/showthread...hreadid=522231