Good day Cimjet,
I am totally new to this VBA and Macros thing. I am trying to set up a spreadsheet that has a master list naming all of the prospects and sub lists broken down depending on the status of each prospect (approved, closed, declined or pending). When a user updates the status of each prospect, how can I have the information auto populate on to the appropriate list? I can send you the spreadsheet for you to review if you like.
Tally38
[quote='Cimjet[_3_];1455226']If you don't get a reply from Max, try posting on this forum
http://answers.microsoft.com/en-us/o.../excel?tab=all
I see him there but not regularly, is last post was Feb.14 I think.
If you don't get a reply from Max, I may have something for you.
Do you know VBA ?
It's a userform that I made and could be modified for your use.
It populates a master sheet and also the sheet you call Rank.
I will keep an eye on this post.
Cimjet
"Matt Simmons" wrote in message
...[color=blue][i]
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
On Friday, January 11, 2008 4:42 PM Banke 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".
On Friday, January 11, 2008 5:13 PM soccerhea wrote:
"Banker" wrote:
On Friday, January 11, 2008 5:34 PM Banke wrote:
I am sorry I didnt see the response you typed below.
"soccerhead" wrote:
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,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
...
On Thursday, January 31, 2008 6:00 PM Max wrote:
wrote
Glad that you enjoyed it, too.
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
---
On Friday, February 01, 2008 10:56 AM Banke wrote:
[color=green][i][color=darkred][i]
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