WOW I really enjoyed the learning this, if my master sheet color coded
how do I get the color to transfer over to the child sheets? 1/31/2008
14:18 central time zone



On Jan 11, 10:07*pm, Max wrote:
Here's one formulas play which delivers the automation that you're after ...

Illustrated in this sample:http://www.freefilehosting.net/download/3a99l
Auto populate from master to child shts.xls

In sheet: WS1 (the "master")
Assume data in cols A to C, data in row2 down,
with the key col = col A (Rank).
Ranks assigned in col A are numbers: 1, 2, 3,etc

List the 4 "Rank" sheetnames in K1 across,
ie: Rank 1, Rank 2, etc (can be in any order)
Ensure these names will match exactly
(except for case) with what's on the sheet tabs

Put in K2: =IF("Rank "&$A2=K$1,ROW(),"")
Copy across as far as required, then fill down to cover the max expected
extent of source 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.

Now to create the child sheets ..

In a new sheet named: Rank 1
With the same col headers pasted into A1:C1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1! $K$1:$IV$1,0)),ROWS($A$1:*A1))),"",INDEX(WS1!A:A,M ATCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$I V*$1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WS N,WS1!$K$1:$IV$1,0)),0)))

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

Cols A to C will return only the lines for "Rank 1" from "WS1",
with all lines neatly bunched at the top

Now, just make a copy of the sheet "Rank 1", rename it as the next rank:
Rank 2, and you'd get the results for "Rank 2". *Repeat the copy rename
sheet process to get the rest of the child sheets (a one-time job). *Adapt to
suit ..
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---"Banker" wrote in message

...



Hi there,
* I am developing a Prospect Database for work that many of my co-workers
will be using for sales. I have created a Work Book that have 5 sheets total.
The first sheet is the Master List containing a list of all of the prospects
information on one page.
* Column 'A' will be used to assign each prospect a number 1 - 4 which we
will be using to rank them in order of priority. I have already created a
drop down box that will allow my co-workers to select the options 1 - 4.

Each sheet after the Master list will be listed Rank1, Rank2, Rank3, and
Rank4.

So... here is my question...

How can I make it so that once the options 1 - 4 have been selected from the
drop down the data for the prospect will then auto populate into the
corresponding sheet?

FOR EXAM: 4Rx10 SHEET 1(the first set of data feilds)
I have chosen to Rank my first propect with a 2 from the drop down box. Now
the information for my prospect will auto populate in my Sheet 3 titled "Rank
2".- Hide quoted text -

- Show quoted text -