1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2007 Question about auto populating from one worksheet to others.

Discussion in 'Business Applications' started by jerang, May 7, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. jerang

    jerang Thread Starter

    Joined:
    May 7, 2010
    Messages:
    15
    I have an Excel 2007 worksheet (main worksheet) that has many columns (15) and rows (800). One column has five different sets of initials. I need to some how figure out a way where I can take that one "Initials" column and then extract three others columns data into five new worksheets (one for each set of initials). So that I can seperate each initials data into its own worksheet (for only that specific set of initals).

    I also need this to auto populate somehow from the main worksheet. I have spent all day trying to figure out a formula for this and I can't seem to make it work. I have figured out how to filter the data but can't seem to get it to auto populate.
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,180
    First Name:
    Wayne
    can you load a sample spreadsheet with dummy data

    I think Vlookup may work - but need to see the data to make sure
     
  3. jerang

    jerang Thread Starter

    Joined:
    May 7, 2010
    Messages:
    15
    Here is an example. There is the main wkst with 9 columns. I need to take the data from columns A,B,C,F,& G and filter them by column E into the other worksheets according the the specified initial per worksheet.
     

    Attached Files:

  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,180
    First Name:
    Wayne
    depends on if you want to do this once or on a regular basis

    You could use filters
    Data>Filter>auto filter
    then use the dropdown to filter column E by the factor you want - then selecting the rows and copying to new sheet
    Repeat for all values in column E
    next
    A macro to do that for you ....

    Is this a ONE OFF ?
     
  5. jerang

    jerang Thread Starter

    Joined:
    May 7, 2010
    Messages:
    15
    This is not a one time thing. I need it to auto populate every time I add a new row of data into the main worksheet onto the following worksheets. I add new lines of data daily and am already doing the cut/copy method and would like to some how have it auto populate on it's own so that I will no longer need to paste to a new worksheet.
     
  6. pradhan

    pradhan

    Joined:
    Apr 5, 2002
    Messages:
    210
    I am fairly certain that your data lends itself to a pivot table. I am not an expert at Pivot Tables but the little I have used them...well, they are pretty incredible for viewing your data in different shapes. For example, you could build a pivot table in a separate worksheet which references the data in your primary worksheet, and then show only one or multiple sets of initials. Take the time to perform MS pivot table tutorial or Google it. This is a very powerful, and underused tool.
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    I put some code in the sample you attached yesterday and a button in the Main sheet

    I did not filter out the initials column in the output, this has to be done in the code but it works.

    Only, if you run if several times you will get duplicate values.

    So it's just to show how I would solve it and further programming needs to be done.

    See if you understand my code and if it helps.

    I did this with 2003 but it works wikt 2007 and up too
     

    Attached Files:

  8. jerang

    jerang Thread Starter

    Joined:
    May 7, 2010
    Messages:
    15
    After playing with it all last night I finally figured out that if I linked my Excel document to an Access database that Access was able to filter and sort everything that I needed. I have never really used pivot tables before but from your advice I will take the tutorial and see if it could help me in the future. Thank you again for your help.
     
  9. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,180
    First Name:
    Wayne
    Pivot Tables are extremely useful for analysing data- certainly worth a look.
    I used to use PTables and also linking and outputting from access, which provdes some very powerful analysis

    Did you try Keebellah macros?

    you can also marked solved - button at top of your first post
     
  10. jerang

    jerang Thread Starter

    Joined:
    May 7, 2010
    Messages:
    15
    This is Great! So much better than linking with Access. I am still trying to figure out how you did it though.
     
  11. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    If you really can't figure it out let me know.
    It's rather simple, one module adds a new sheet if it doesn't already exist the other module just appends
     
  12. jerang

    jerang Thread Starter

    Joined:
    May 7, 2010
    Messages:
    15
    I have figured out how to change some aspects of the code to fit my actual worksheet. However, i cannot figure out how your got the GoForIt button added onto the main wkst?
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,568
    First Name:
    Hans
    Using the forms command bar there is button, chosse this one, place it on the sheet and when prompted associate the macro to it
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/921661

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice