PC Review


Reply
Thread Tools Rate Thread

Auto Populate from a Mater List into Sub Sheets

 
 
Banker
Guest
Posts: n/a
 
      11th Jan 2008
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".
 
Reply With Quote
 
 
 
 
soccerhead
Guest
Posts: n/a
 
      11th Jan 2008


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

 
Reply With Quote
 
 
 
 
Banker
Guest
Posts: n/a
 
      11th Jan 2008
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".

 
Reply With Quote
 
Max
Guest
Posts: n/a
 
      12th Jan 2008
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,MATCH(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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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".

 
Reply With Quote
 
Jonathan_Pyron@sbcglobal.net
Guest
Posts: n/a
 
      31st Jan 2008
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 <(E-Mail Removed)> 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,MATCH(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
> Singaporehttp://savefile.com/projects/236895
> xdemechanik
> ---"Banker" <(E-Mail Removed)> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > 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 whichwe
> > 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 fromthe
> > 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 -


 
Reply With Quote
 
Max
Guest
Posts: n/a
 
      1st Feb 2008
<(E-Mail Removed)> 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
---


 
Reply With Quote
 
Banker
Guest
Posts: n/a
 
      1st Feb 2008
MAX MAX MAX,
YOU ARE MY HERO!!
Sorry for the screaming but you have truly helped me out! You have gone
above and beyond! For anyone out there that needs help, Max is your man. He
contacted me on my personal email and fixed my spread sheet and sent it back
to me no questions asked. I dont want to volunteer him for any extra work but
I promise you will not be dissapointed with his advise. He, in a matter of a
day, cracked the function code that I had been working on for 3 weeks!

Hats off to great people; and max you definately fall into that categorie
for me!

The Banker


"Max" wrote:

> <(E-Mail Removed)> 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
> ---
>
>
>

 
Reply With Quote
 
Max
Guest
Posts: n/a
 
      1st Feb 2008
welcome, Banker.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


 
Reply With Quote
 
mscureman
Guest
Posts: n/a
 
      7th Feb 2008
Max, I am facing a similar dilemma and have applied your solution to the
best of my abilities so far, but I am having trouble knowing what to replace
"Filename" with when you define the WSN name. Also, my version of Banker's
"rank 1, rank 2, etc." is non-numeric (E, P, M, etc.) and not situated in the
first column, but in column E. Therefor, if Column E contains "P", the
entire row should propogate onto the sheet named "Payroll", if it contains an
"E", it goes to the sheet named "Expenses" and "M" for "Materials". I went
through your formulas on the Rank 1, Rank 2, Rank 3 and Rank 4 worksheets and
am trying to identify the variables that I should change for my sheets, but I
think I'm stuck on the WSN thing first. BTW, this is a modification of the
checkbook register for Excel 97 or later (I'm using 2002) that is found on
the microsoft free templates site. The modification is to allow tagging of
each transaction as a specific type so that it can be extracted to and viewed
on a separate worksheet with other transactions of its type. I've also added
a column with a formula that keeps track of the balance of the rows that have
an "X" in the "cleared" column to make balancing the checkbook automatic.

Mark
 
Reply With Quote
 
Max
Guest
Posts: n/a
 
      7th Feb 2008
Here's a link to a sample customised to suit your specs with key col = col E:
http://www.freefilehosting.net/download/3bi99
Auto_populate_from_master_to_child_shts_key col E.xls

You don't need to touch this part:
> .. replace "Filename" with when you define the WSN name.

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mscureman" wrote:
> Max, I am facing a similar dilemma and have applied your solution to the
> best of my abilities so far, but I am having trouble knowing what to replace
> "Filename" with when you define the WSN name. Also, my version of Banker's
> "rank 1, rank 2, etc." is non-numeric (E, P, M, etc.) and not situated in the
> first column, but in column E. Therefor, if Column E contains "P", the
> entire row should propogate onto the sheet named "Payroll", if it contains an
> "E", it goes to the sheet named "Expenses" and "M" for "Materials". I went
> through your formulas on the Rank 1, Rank 2, Rank 3 and Rank 4 worksheets and
> am trying to identify the variables that I should change for my sheets, but I
> think I'm stuck on the WSN thing first. BTW, this is a modification of the
> checkbook register for Excel 97 or later (I'm using 2002) that is found on
> the microsoft free templates site. The modification is to allow tagging of
> each transaction as a specific type so that it can be extracted to and viewed
> on a separate worksheet with other transactions of its type. I've also added
> a column with a formula that keeps track of the balance of the rows that have
> an "X" in the "cleared" column to make balancing the checkbook automatic.
>
> Mark

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cannot see network places or workgroup computers no mater what i d =?Utf-8?B?a2V3bGVzdF9jaGlfZ3V5?= Windows XP Networking 1 21st Aug 2005 10:54 PM
Mater Pages and Cross-page Postbacks. =?Utf-8?B?RWQ=?= Microsoft ASP .NET 1 25th Jul 2005 08:49 PM
microAtx motherboards: Does size mater? Machine Messiah Computer Hardware 3 8th Apr 2004 06:34 AM
Link Child and Mater field JC Microsoft Access Reports 2 3rd Nov 2003 10:37 PM
Re: Diable Mater Browser Dave Patrick Microsoft Windows 2000 Setup 0 23rd Sep 2003 08:36 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:14 PM.