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".

"Banker" wrote:

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".

I am sorry I didnt see the response you typed below.

"soccerhead" wrote:



"Banker" wrote:

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".

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,MA TCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV $1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN, 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
Singapore
http://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".

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 -

wrote
WOW I really enjoyed the learning this

Glad that you enjoyed it, too.

if my master sheet color coded, how do I
get the color to transfer over to the child sheets?

If the color coding on the master/parent is via conditional formatting
criteria, you could simply replicate that CF on the child sheets. If it's
not, then it's not possible as formulas do not return formatting. You would
need vba. You could try posting in excel.programming for ideas.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi Max
Can you please help me with a problem, I need to enter data in one cell '2' and then have it look at an amount in one column, do a multiplication in two columns and then in the next row use the data from the row above.
800 x 8% = 64 (val x 8 = growth)
64 xx 80% = 51 (growth x unlock)
and put the 864 on the next line and go again.
Is there anyway of doing this Max?
Would greatly appreciate any help
Regards Denni
'2'
Value Growth Unlock
Start $800 $64 $51
Y1 $864 $69 $55
Y2

Start value assumed in B2: 800

In C2: =B2*8%
In D2: =C2*80%
Copy C2:D2 down

In B3: =SUM(B2:C2)
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
<denni taylor wrote in message ...
Hi Max
Can you please help me with a problem, I need to enter data in one cell
'2' and then have it look at an amount in one column, do a multiplication
in two columns and then in the next row use the data from the row above.
800 x 8% = 64 (val x 8 = growth)
64 xx 80% = 51 (growth x unlock)
and put the 864 on the next line and go again.
Is there anyway of doing this Max?
Would greatly appreciate any help
Regards Denni
'2'
Value Growth Unlock
Start $800 $64 $51
Y1 $864 $69 $55
Y2