Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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 ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
automatic color change in cells using a drop down list | Excel Worksheet Functions | |||
Multiple sheets selected | Excel Discussion (Misc queries) | |||
To view different sheets using a drop down list | Excel Worksheet Functions | |||
Drop List Referencing | Excel Worksheet Functions |