Auto Populate from a Mater List into Sub Sheets

Discussion in 'Microsoft Excel Worksheet Functions' started by Banker, Jan 11, 2008.

  1. Banker

    Banker Guest

    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, Jan 11, 2008
    #1
    1. Advertisements

  2. Banker

    soccerhead Guest

    "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".
     
    soccerhead, Jan 11, 2008
    #2
    1. Advertisements

  3. Banker

    Banker Guest

    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".
     
    Banker, Jan 11, 2008
    #3
  4. Banker

    Max Guest

    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" <> wrote in message
    news:...
    > 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".
     
    Max, Jan 12, 2008
    #4
  5. Banker

    Guest

    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,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" <> wrote in message
    >
    > news:...
    >
    >
    >
    > > 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 -
     
    , Jan 31, 2008
    #5
  6. Banker

    Max Guest

    <> 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
    ---
     
    Max, Jan 31, 2008
    #6
  7. Banker

    Banker Guest

    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:

    > <> 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
    > ---
    >
    >
    >
     
    Banker, Feb 1, 2008
    #7
  8. Banker

    Max Guest

    Max, Feb 1, 2008
    #8
  9. Banker

    mscureman Guest

    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
     
    mscureman, Feb 6, 2008
    #9
  10. Banker

    Max Guest

    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
     
    Max, Feb 7, 2008
    #10
  11. Banker

    mscureman Guest

    Wow! That sure was fast! Thanks! Well, I think I'm closer now, but I'm
    still not getting the data to transfer from the main register (sheet named
    "Check Register") even when I changed the sheet name to WS1 to match your
    formula. For further clarification, my rows to be transferred begin on Row 6
    and go through 92 or so (but this will grow as the year goes by). Also, I
    have a blank column K and my row formula begins in L (L6). Each of the
    sheets looks exactly the same as the main sheet, so that data will begin to
    poplulate at B6. I adjusted the J in your offset parameter to K, but I must
    be missing something else.

    "Max" wrote:

    > 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
     
    mscureman, Feb 7, 2008
    #11
  12. Banker

    Max Guest

    Max, Feb 7, 2008
    #12
  13. Banker

    mscureman Guest

    mscureman, Feb 7, 2008
    #13
  14. Banker

    Max Guest

    Here you go, all ready and functioning:
    http://www.freefilehosting.net/download/3bj2c
    Checkbook_register2a.xls

    In Payroll,
    In B6, copied across/filled down:
    =IF(ISERROR(SMALL(OFFSET('Check Register'!$K:$K,,MATCH(WSN,'Check
    Register'!$L$4:$IV$4,0)),ROWS($1:1))),"",
    INDEX('Check Register'!B:B,MATCH(SMALL(OFFSET('Check
    Register'!$K:$K,,MATCH(WSN,'Check Register'!$L$4:$IV$4,0)),ROWS($1:1)),
    OFFSET('Check Register'!$K:$K,,MATCH(WSN,'Check Register'!$L$4:$IV$4,0)),0)))

    The sheet: Payroll is copied & renamed as: Expenses, Materials
    to easily get the lines for the other 2 sheets.
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
     
    Max, Feb 8, 2008
    #14
  15. Banker

    mscureman Guest

    It works perfectly, of course. Thank you so much, Max. How close was I?
    Did you have to change much of what I had in place? Was I going in the right
    direction, at least?

    "Max" wrote:

    > Here you go, all ready and functioning:
    > http://www.freefilehosting.net/download/3bj2c
    > Checkbook_register2a.xls
    >
    > In Payroll,
    > In B6, copied across/filled down:
    > =IF(ISERROR(SMALL(OFFSET('Check Register'!$K:$K,,MATCH(WSN,'Check
    > Register'!$L$4:$IV$4,0)),ROWS($1:1))),"",
    > INDEX('Check Register'!B:B,MATCH(SMALL(OFFSET('Check
    > Register'!$K:$K,,MATCH(WSN,'Check Register'!$L$4:$IV$4,0)),ROWS($1:1)),
    > OFFSET('Check Register'!$K:$K,,MATCH(WSN,'Check Register'!$L$4:$IV$4,0)),0)))
    >
    > The sheet: Payroll is copied & renamed as: Expenses, Materials
    > to easily get the lines for the other 2 sheets.
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
     
    mscureman, Feb 8, 2008
    #15
  16. Banker

    Max Guest

    Welcome. Yes, you were in the right direction. Just a couple of little
    tweaks, and you're home.
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "mscureman" <> wrote in message
    news:...
    > It works perfectly, of course. Thank you so much, Max. How close was I?
    > Did you have to change much of what I had in place? Was I going in the
    > right
    > direction, at least?
     
    Max, Feb 8, 2008
    #16
  17. Banker

    kola gola Guest

    Re: Re: Auto Populate from a Mater List into Sub Sheets

    Hi Banker,

    Could you please provide another link, this one does not work.

    Thanks,

    Kola

    > 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,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" <> wrote in message
    >>>> news:...



    >>>>> 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:


    >>>>>> 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:



    >>>>>>> On Friday, February 01, 2008 1:02 PM Max wrote:


    >>>>>>> welcome, Banker.
    >>>>>>> --
    >>>>>>> Max
    >>>>>>> Singapore
    >>>>>>> http://savefile.com/projects/236895
    >>>>>>> xdemechanik
    >>>>>>> ---



    >>>>>>>> On Wednesday, February 06, 2008 6:44 PM mscurema 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



    >>>>>>>>> On Wednesday, February 06, 2008 9:04 PM Jonathan_Pyro wrote:


    >>>>>>>>> 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=A0pm, Max <> wrote:
    >>>>>>>>> .
    >>>>>>>>> A1"=AD)
    >>>>>>>>> om
    >>>>>>>>> $1:=ADA1))),"",INDEX(WS1!A:A,MATCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$=
    >>>>>>>>> 1:$IV=AD$1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0))=
    >>>>>>>>> ,0)))
    >>>>>>>>> e
    >>>>>>>>> pt to
    >>>>>>>>> ers
    >>>>>>>>> otal.
    >>>>>>>>> ects
    >>>>>>>>> we
    >>>>>>>>> a
    >>>>>>>>>
    >>>>>>>>>
    >>>>>>>>> the
    >>>>>>>>> Now
    >>>>>>>>> "Rank



    >>>>>>>>>> On Thursday, February 07, 2008 6:25 AM demechani wrote:


    >>>>>>>>>> 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:
    >>>>>>>>>> --
    >>>>>>>>>> Max
    >>>>>>>>>> Singapore
    >>>>>>>>>> http://savefile.com/projects/236895
    >>>>>>>>>> xdemechanik
    >>>>>>>>>> ---
    >>>>>>>>>> "mscureman" wrote:



    >>>>>>>>>>> On Thursday, February 07, 2008 8:25 AM mscurema wrote:


    >>>>>>>>>>> Wow! That sure was fast! Thanks! Well, I think I'm closer now, but I'm
    >>>>>>>>>>> still not getting the data to transfer from the main register (sheet named
    >>>>>>>>>>> "Check Register") even when I changed the sheet name to WS1 to match your
    >>>>>>>>>>> formula. For further clarification, my rows to be transferred begin on Row 6
    >>>>>>>>>>> and go through 92 or so (but this will grow as the year goes by). Also, I
    >>>>>>>>>>> have a blank column K and my row formula begins in L (L6). Each of the
    >>>>>>>>>>> sheets looks exactly the same as the main sheet, so that data will begin to
    >>>>>>>>>>> poplulate at B6. I adjusted the J in your offset parameter to K, but I must
    >>>>>>>>>>> be missing something else.
    >>>>>>>>>>>
    >>>>>>>>>>> "Max" wrote:



    >>>>>>>>>>>> On Thursday, February 07, 2008 4:45 PM Max wrote:


    >>>>>>>>>>>> Post a link to your sample, use:
    >>>>>>>>>>>> http://www.freefilehosting.net/
    >>>>>>>>>>>> --
    >>>>>>>>>>>> Max
    >>>>>>>>>>>> Singapore
    >>>>>>>>>>>> http://savefile.com/projects/236895
    >>>>>>>>>>>> xdemechanik
    >>>>>>>>>>>> ---



    >>>>>>>>>>>>> On Thursday, February 07, 2008 5:48 PM mscurema wrote:


    >>>>>>>>>>>>> Here it is:
    >>>>>>>>>>>>>
    >>>>>>>>>>>>> http://www.freefilehosting.net/download/3bj13
    >>>>>>>>>>>>>
    >>>>>>>>>>>>> "Max" wrote:



    >>>>>>>>>>>>>> On Thursday, February 07, 2008 8:54 PM demechani wrote:


    >>>>>>>>>>>>>> Here you go, all ready and functioning:
    >>>>>>>>>>>>>> http://www.freefilehosting.net/download/3bj2c
    >>>>>>>>>>>>>> Checkbook_register2a.xls
    >>>>>>>>>>>>>>
    >>>>>>>>>>>>>> In Payroll,
    >>>>>>>>>>>>>> In B6, copied across/filled down:
    >>>>>>>>>>>>>> =IF(ISERROR(SMALL(OFFSET('Check Register'!$K:$K,,MATCH(WSN,'Check
    >>>>>>>>>>>>>> Register'!$L$4:$IV$4,0)),ROWS($1:1))),"",
    >>>>>>>>>>>>>> INDEX('Check Register'!B:B,MATCH(SMALL(OFFSET('Check
    >>>>>>>>>>>>>> Register'!$K:$K,,MATCH(WSN,'Check Register'!$L$4:$IV$4,0)),ROWS($1:1)),
    >>>>>>>>>>>>>> OFFSET('Check Register'!$K:$K,,MATCH(WSN,'Check Register'!$L$4:$IV$4,0)),0)))
    >>>>>>>>>>>>>>
    >>>>>>>>>>>>>> The sheet: Payroll is copied & renamed as: Expenses, Materials
    >>>>>>>>>>>>>> to easily get the lines for the other 2 sheets.
    >>>>>>>>>>>>>> --
    >>>>>>>>>>>>>> Max
    >>>>>>>>>>>>>> Singapore
    >>>>>>>>>>>>>> http://savefile.com/projects/236895
    >>>>>>>>>>>>>> xdemechanik
    >>>>>>>>>>>>>> ---



    >>>>>>>>>>>>>>> On Friday, February 08, 2008 8:08 AM mscurema wrote:


    >>>>>>>>>>>>>>> It works perfectly, of course. Thank you so much, Max. How close was I?
    >>>>>>>>>>>>>>> Did you have to change much of what I had in place? Was I going in the right
    >>>>>>>>>>>>>>> direction, at least?
    >>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>> "Max" wrote:



    >>>>>>>>>>>>>>>> On Friday, February 08, 2008 8:39 AM Max wrote:


    >>>>>>>>>>>>>>>> Welcome. Yes, you were in the right direction. Just a couple of little
    >>>>>>>>>>>>>>>> tweaks, and you are home.
    >>>>>>>>>>>>>>>> --
    >>>>>>>>>>>>>>>> Max
    >>>>>>>>>>>>>>>> Singapore
    >>>>>>>>>>>>>>>> http://savefile.com/projects/236895
    >>>>>>>>>>>>>>>> xdemechanik
    >>>>>>>>>>>>>>>> ---



    >>>>>>>>>>>>>>>>> On Wednesday, August 20, 2008 7:27 AM denni taylor wrote:


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



    >>>>>>>>>>>>>>>>>> On Wednesday, July 14, 2010 8:08 AM arshi khan wrote:


    >>>>>>>>>>>>>>>>>> Friend,
    >>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>> I have problem. I would be thankful for any help you can offer. I have to enter data in master sheet and based on data entered in third column (Received By) and forth column (Status), the data should be saved in specific sheets concerning them.
    >>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>> Third column will have different names and the name which will be entered will have his own sheet exactly as master sheet.
    >>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>> Fourth column will have 3 type of status (for example finalized, finished or pending). Depending upon the type of status, we have three separate sheet and the data will also go in these sheets.
    >>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>> Enquiry No. Customer Name Received By Status
    >>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>> I would be thankful for your guidance.
    >>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>> Regards,
    >>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>> Arshi



    >>>>>>>>>>>>>>>>>>> On Wednesday, August 04, 2010 8:02 PM Tiffaney Matthews wrote:


    >>>>>>>>>>>>>>>>>>> Hi Guys
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>> I have a sheet of teams and the member names. I have a sheet with names and data. I need the data sheet to find it's equivalent on the teams sheet and return the data to a seperate sheet for that team.
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>> Any ideas?



    >>>>>>>>>>>>>>>>>>> Submitted via EggHeadCafe
    >>>>>>>>>>>>>>>>>>> Review of Redgate ANTS Performance Profiler 6
    >>>>>>>>>>>>>>>>>>> http://www.eggheadcafe.com/tutorial...w-of-redgate-ants-performance-profiler-6.aspx
     
    kola gola, Nov 14, 2010
    #17
  18. Banker

    Cimjet Guest

    Re: Re: Auto Populate from a Mater List into Sub Sheets

    If you don't get a reply from Max, try posting on this forum
    http://answers.microsoft.com/en-us/office/forum/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
    news:...
    > 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,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" <> wrote in message
    >>>>> news:...

    >
    >
    >>>>>> 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:

    >
    >>>>>>> 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:

    >
    >
    >>>>>>>> On Friday, February 01, 2008 1:02 PM Max wrote:

    >
    >>>>>>>> welcome, Banker.
    >>>>>>>> --
    >>>>>>>> Max
    >>>>>>>> Singapore
    >>>>>>>> http://savefile.com/projects/236895
    >>>>>>>> xdemechanik
    >>>>>>>> ---

    >
    >
    >>>>>>>>> On Wednesday, February 06, 2008 6:44 PM mscurema 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

    >
    >
    >>>>>>>>>> On Wednesday, February 06, 2008 9:04 PM Jonathan_Pyro wrote:

    >
    >>>>>>>>>> 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=A0pm, Max <> wrote:
    >>>>>>>>>> .
    >>>>>>>>>> A1"=AD)
    >>>>>>>>>> om
    >>>>>>>>>> $1:=ADA1))),"",INDEX(WS1!A:A,MATCH(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$=
    >>>>>>>>>> 1:$IV=AD$1,0)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1!$K$1:$IV$1,0))=
    >>>>>>>>>> ,0)))
    >>>>>>>>>> e
    >>>>>>>>>> pt to
    >>>>>>>>>> ers
    >>>>>>>>>> otal.
    >>>>>>>>>> ects
    >>>>>>>>>> we
    >>>>>>>>>> a
    >>>>>>>>>>
    >>>>>>>>>>
    >>>>>>>>>> the
    >>>>>>>>>> Now
    >>>>>>>>>> "Rank

    >
    >
    >>>>>>>>>>> On Thursday, February 07, 2008 6:25 AM demechani wrote:

    >
    >>>>>>>>>>> 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:
    >>>>>>>>>>> --
    >>>>>>>>>>> Max
    >>>>>>>>>>> Singapore
    >>>>>>>>>>> http://savefile.com/projects/236895
    >>>>>>>>>>> xdemechanik
    >>>>>>>>>>> ---
    >>>>>>>>>>> "mscureman" wrote:

    >
    >
    >>>>>>>>>>>> On Thursday, February 07, 2008 8:25 AM mscurema wrote:

    >
    >>>>>>>>>>>> Wow! That sure was fast! Thanks! Well, I think I'm closer now,
    >>>>>>>>>>>> but I'm
    >>>>>>>>>>>> still not getting the data to transfer from the main register
    >>>>>>>>>>>> (sheet named
    >>>>>>>>>>>> "Check Register") even when I changed the sheet name to WS1 to
    >>>>>>>>>>>> match your
    >>>>>>>>>>>> formula. For further clarification, my rows to be transferred
    >>>>>>>>>>>> begin on Row 6
    >>>>>>>>>>>> and go through 92 or so (but this will grow as the year goes by).
    >>>>>>>>>>>> Also, I
    >>>>>>>>>>>> have a blank column K and my row formula begins in L (L6). Each of
    >>>>>>>>>>>> the
    >>>>>>>>>>>> sheets looks exactly the same as the main sheet, so that data will
    >>>>>>>>>>>> begin to
    >>>>>>>>>>>> poplulate at B6. I adjusted the J in your offset parameter to K,
    >>>>>>>>>>>> but I must
    >>>>>>>>>>>> be missing something else.
    >>>>>>>>>>>>
    >>>>>>>>>>>> "Max" wrote:

    >
    >
    >>>>>>>>>>>>> On Thursday, February 07, 2008 4:45 PM Max wrote:

    >
    >>>>>>>>>>>>> Post a link to your sample, use:
    >>>>>>>>>>>>> http://www.freefilehosting.net/
    >>>>>>>>>>>>> --
    >>>>>>>>>>>>> Max
    >>>>>>>>>>>>> Singapore
    >>>>>>>>>>>>> http://savefile.com/projects/236895
    >>>>>>>>>>>>> xdemechanik
    >>>>>>>>>>>>> ---

    >
    >
    >>>>>>>>>>>>>> On Thursday, February 07, 2008 5:48 PM mscurema wrote:

    >
    >>>>>>>>>>>>>> Here it is:
    >>>>>>>>>>>>>>
    >>>>>>>>>>>>>> http://www.freefilehosting.net/download/3bj13
    >>>>>>>>>>>>>>
    >>>>>>>>>>>>>> "Max" wrote:

    >
    >
    >>>>>>>>>>>>>>> On Thursday, February 07, 2008 8:54 PM demechani wrote:

    >
    >>>>>>>>>>>>>>> Here you go, all ready and functioning:
    >>>>>>>>>>>>>>> http://www.freefilehosting.net/download/3bj2c
    >>>>>>>>>>>>>>> Checkbook_register2a.xls
    >>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>> In Payroll,
    >>>>>>>>>>>>>>> In B6, copied across/filled down:
    >>>>>>>>>>>>>>> =IF(ISERROR(SMALL(OFFSET('Check
    >>>>>>>>>>>>>>> Register'!$K:$K,,MATCH(WSN,'Check
    >>>>>>>>>>>>>>> Register'!$L$4:$IV$4,0)),ROWS($1:1))),"",
    >>>>>>>>>>>>>>> INDEX('Check Register'!B:B,MATCH(SMALL(OFFSET('Check
    >>>>>>>>>>>>>>> Register'!$K:$K,,MATCH(WSN,'Check
    >>>>>>>>>>>>>>> Register'!$L$4:$IV$4,0)),ROWS($1:1)),
    >>>>>>>>>>>>>>> OFFSET('Check Register'!$K:$K,,MATCH(WSN,'Check
    >>>>>>>>>>>>>>> Register'!$L$4:$IV$4,0)),0)))
    >>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>> The sheet: Payroll is copied & renamed as: Expenses, Materials
    >>>>>>>>>>>>>>> to easily get the lines for the other 2 sheets.
    >>>>>>>>>>>>>>> --
    >>>>>>>>>>>>>>> Max
    >>>>>>>>>>>>>>> Singapore
    >>>>>>>>>>>>>>> http://savefile.com/projects/236895
    >>>>>>>>>>>>>>> xdemechanik
    >>>>>>>>>>>>>>> ---

    >
    >
    >>>>>>>>>>>>>>>> On Friday, February 08, 2008 8:08 AM mscurema wrote:

    >
    >>>>>>>>>>>>>>>> It works perfectly, of course. Thank you so much, Max. How
    >>>>>>>>>>>>>>>> close was I?
    >>>>>>>>>>>>>>>> Did you have to change much of what I had in place? Was I
    >>>>>>>>>>>>>>>> going in the right
    >>>>>>>>>>>>>>>> direction, at least?
    >>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>> "Max" wrote:

    >
    >
    >>>>>>>>>>>>>>>>> On Friday, February 08, 2008 8:39 AM Max wrote:

    >
    >>>>>>>>>>>>>>>>> Welcome. Yes, you were in the right direction. Just a couple
    >>>>>>>>>>>>>>>>> of little
    >>>>>>>>>>>>>>>>> tweaks, and you are home.
    >>>>>>>>>>>>>>>>> --
    >>>>>>>>>>>>>>>>> Max
    >>>>>>>>>>>>>>>>> Singapore
    >>>>>>>>>>>>>>>>> http://savefile.com/projects/236895
    >>>>>>>>>>>>>>>>> xdemechanik
    >>>>>>>>>>>>>>>>> ---

    >
    >
    >>>>>>>>>>>>>>>>>> On Wednesday, August 20, 2008 7:27 AM denni taylor wrote:

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

    >
    >
    >>>>>>>>>>>>>>>>>>> On Wednesday, July 14, 2010 8:08 AM arshi khan wrote:

    >
    >>>>>>>>>>>>>>>>>>> Friend,
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>> I have problem. I would be thankful for any help you can
    >>>>>>>>>>>>>>>>>>> offer. I have to enter data in master sheet and based on
    >>>>>>>>>>>>>>>>>>> data entered in third column (Received By) and forth column
    >>>>>>>>>>>>>>>>>>> (Status), the data should be saved in specific sheets
    >>>>>>>>>>>>>>>>>>> concerning them.
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>> Third column will have different names and the name which
    >>>>>>>>>>>>>>>>>>> will be entered will have his own sheet exactly as master
    >>>>>>>>>>>>>>>>>>> sheet.
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>> Fourth column will have 3 type of status (for example
    >>>>>>>>>>>>>>>>>>> finalized, finished or pending). Depending upon the type of
    >>>>>>>>>>>>>>>>>>> status, we have three separate sheet and the data will also
    >>>>>>>>>>>>>>>>>>> go in these sheets.
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>> Enquiry No. Customer Name Received By Status
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>> I would be thankful for your guidance.
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>> Regards,
    >>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>> Arshi

    >
    >
    >>>>>>>>>>>>>>>>>>>> On Wednesday, August 04, 2010 8:02 PM Tiffaney Matthews
    >>>>>>>>>>>>>>>>>>>> wrote:

    >
    >>>>>>>>>>>>>>>>>>>> Hi Guys
    >>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>> I have a sheet of teams and the member names. I have a
    >>>>>>>>>>>>>>>>>>>> sheet with names and data. I need the data sheet to find
    >>>>>>>>>>>>>>>>>>>> it's equivalent on the teams sheet and return the data to a
    >>>>>>>>>>>>>>>>>>>> seperate sheet for that team.
    >>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>> Any ideas?

    >
    >
    >>>>>>>>>>>>>>>>>>>>> On Sunday, November 14, 2010 12:06 PM kola gola wrote:

    >
    >>>>>>>>>>>>>>>>>>>>> Hi Banker,
    >>>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>>> Could you please provide another link, this one does not
    >>>>>>>>>>>>>>>>>>>>> work.
    >>>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>>> Thanks,
    >>>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>>>
    >>>>>>>>>>>>>>>>>>>>> Kola

    >
    >
    >>>>>>>>>>>>>>>>>>>>>> On Wednesday, February 22, 2012 7:24 AM Matt Simmons
    >>>>>>>>>>>>>>>>>>>>>> wrote:

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

    >
    >
    >
     
    Cimjet, Feb 22, 2012
    #18
  19. Banker

    Max Guest

    Ahh, managed to suss out a copy ... here it is:
    http://cjoint.com/?BBCpyDymvBm
    Auto populate from master to child shts.xls

    Link above is good for 2 weeks, I believe

    On Feb 22, 8:38 pm, Matt Simmons <> wrote:
    > 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:
    >
    > Autopopulatefrom master to child shts.xls
    >
    > Auto_populate_from_master_to_child_shts_key col E.xls
    >
    > They are gone from freefilehosting.
    >
    > Thanks,
    >
    > -Matt
     
    Max, Feb 28, 2012
    #19
  20. Banker

    Max Guest

    Thanks again for the prompt. I've replied to the OP with a d/l link.

    As there's always room for alternative approaches, vba or otherwise,
    why not share your option with us? I'd be as keen as OP
    Just one request though, that the sample file (if any) be saved as XL
    2003 (.xls), cheers

    On Feb 22, 11:25 pm, "Cimjet" <> wrote:
    > If you don't get a reply from Max,
    > try posting on this forumhttp://answers.microsoft.com/en-us/office/forum/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.
     
    Max, Feb 28, 2012
    #20
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Auto-populate, Auto-copy or Auto-fill?

    Guest, Aug 10, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    576
    Guest
    Aug 10, 2007
  2. CrashOz

    Auto populate data from a table into cells after a match is found

    CrashOz, Jan 15, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    5
    Views:
    207
    Pete_UK
    Jan 15, 2008
  3. Johnny

    Auto holiday days populate on other sheets

    Johnny, Apr 12, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    221
    Johnny
    Apr 13, 2008
  4. foursight

    how do you auto populate tasks into a calendar

    foursight, Jun 12, 2009, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    500
    Gord Dibben
    Jun 12, 2009
  5. Guest

    Re: Auto Populate Sheets by Employer

    Guest, Mar 14, 2012, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    467
    Guest
    Mar 14, 2012
Loading...

Share This Page