M
Matt Simmons
Max are you still there?! I know this is an old post but I love this elegant solution, for the life of me I cannot get to work based on instructions, any chance you could re-post one of the original files either:
Auto populate from master to child shts.xls
Auto_populate_from_master_to_child_shts_key col E.xls
They are gone from freefilehosting.
Thanks,
-Matt
Auto populate from master to child shts.xls
Auto_populate_from_master_to_child_shts_key col E.xls
They are gone from freefilehosting.
Thanks,
-Matt
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".
On Friday, January 11, 2008 11:07 PM demechani 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
Singapore
http://savefile.com/projects/236895
xdemechanik