Drop in certain sheets
 

I have several sheets already setup and ready to accept info entered from one
main sheet. Here's the problem I have. I assign cases to officers in my
building. I have created sheets for each of the officers. So that the
clients will go into the correct location. I enter the info on main sheet
where most of this information is going to be entered such as last name,
first name, case number, and so on. How do I get the information for each of
them to drop into the correct sheet that I want it to go to.

Perhaps this previous post might give you
some possibilities to explore further: http://tinyurl.com/5ejr2
(the key column in your case would be the officers' names)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kenny Dee" wrote in message
...
I have several sheets already setup and ready to accept info entered from
one
main sheet. Here's the problem I have. I assign cases to officers in my
building. I have created sheets for each of the officers. So that the
clients will go into the correct location. I enter the info on main sheet
where most of this information is going to be entered such as last name,
first name, case number, and so on. How do I get the information for each
of
them to drop into the correct sheet that I want it to go to.

I've printed and try to understand what was mention on that web page but it
was abit hard to understand. Can you provide me with another web site, with
more detail or visual tables?

"Max" wrote:

Perhaps this previous post might give you
some possibilities to explore further: http://tinyurl.com/5ejr2
(the key column in your case would be the officers' names)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kenny Dee" wrote in message
...
I have several sheets already setup and ready to accept info entered from
one
main sheet. Here's the problem I have. I assign cases to officers in my
building. I have created sheets for each of the officers. So that the
clients will go into the correct location. I enter the info on main sheet
where most of this information is going to be entered such as last name,
first name, case number, and so on. How do I get the information for each
of
them to drop into the correct sheet that I want it to go to.

Better still <g, how about a working sample ?

Just drop me a line at either:
demechanik <atyahoo<dotcom, or
xdemechanik <atyahoo<dotcom

and I'll send it to you via private email
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kenny Dee" wrote in message
...
I've printed and try to understand what was mention on that web page but
it
was abit hard to understand. Can you provide me with another web site,
with
more detail or visual tables?

Here's a set-up to try, customized to your post ..

In Sheet1
------------
Assume the sample table below is in cols A to D, data from row2 down, with
the key column being col D: "Assigned to", which houses the names of the
officers assigned to, viz:

LastN FirstN Case# Assigned to
Gee Kevin 1111 Officer1
Martin Peter 1112 Officer2
Hays John 1113 Officer2
Factor Max 1114 Officer1
O'Neal Shack 1115 Officer3
Kenya Long 1116 Officer3
etc

List across in say, F1:H1
the 4 officers (assigned): Officer1, Officer2, Officer3, Officer4

Put in F2: =IF($D2="","",IF($D2=F$1,ROW(),""))

Copy F2 across to H2, then fill down by a safe "max"
number of rows that data is ever expected in cols A to D,
say, down to H100 ?

In a new sheet named: Officer1
------------------------------*---------
Let's reserve cell A1 to pull in the sheetname

Put in A1:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32)

(This'll extract the sheetname: Officer1 into A1.
But you need to save the file first)

Copy Paste the same col headers from Sheet1
into A2:C2, i.e.: LastN, FirstN, Case#, Assigned to

Put in A3:

=IF(ISERROR(SMALL(OFFSET(Sheet1!$E:$E,0,MATCH($A$1 ,Sheet1!$F$1:$I$1,0)),ROWS
($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMALL(OFFSET (Sheet1!$E:$E,0,MATCH($A$1
,Sheet1!$F$1:$I$1,0)),ROWS($A$1:A1)),OFFSET(Sheet1 !$E:$E,0,MATCH($A$1,Sheet1
!$F$1:$I$1,0)),0)))

Note: You'd need to correct / restore the couple of inevitable line wraps /
line breaks when you copy paste the above formula into A3

Copy A3 across to C3, fill down by as many rows as was done in Sheet1, viz.
down to C101

You'll see that cols A to C (in row3 down)
will auto-return the "filtered" rows for "Officer1" from Sheet1,
i.e. for the sample data-set above, it'll appear as:

Officer1
LastN FirstN Case#
Gee Kevin 1111
Factor Max 1114
(rest are blank rows)

Now, just duplicate / make a copy of the sheet: Officer1
rename it as: Officer2
and you'll get the "filtered" rows for "Officer2"

Officer2
LastN FirstN Case#
Martin Peter 1112
Hays John 1113
(rest are blank rows)

Repeat the sheet duplication / renaming
for the remaining "Officer3"
which yields:

Officer3
LastN FirstN Case#
O'Neal Shack 1115
Kenya Long 1116
(rest are blank rows)

Data entered into Sheet1 will automatically appear
in the correct individual Officer's sheet

As mentioned earlier, if you want a working sample,
just drop me a line ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Kenny Dee" wrote in message
...
I've printed and try to understand what was mention
on that web page but it was abit hard to understand.
Can you provide me with another web site, with
more detail or visual tables?

Typo correction:

List across in say, F1:H1
the 4 officers (assigned): Officer1, Officer2, Officer3, Officer4

2nd line above should read as:
the 3 officers (assigned): Officer1, Officer2, Officer3

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----