+ Reply to Thread
Results 1 to 10 of 10

Macro Help: Auto Populate Summary Table

  1. 08-04-2014, 01:22 PM #1
    pdowney
    pdowney is offline
    Registered User
    Join Date
    08-04-2014
    Location
    Washington, DC
    MS-Off Ver
    Office 2010
    Posts
    8

    Macro Help: Auto Populate Summary Table

    Hi All,

    I'm working on a template that has 2 tables. First table is a log of all the hours someone has worked on specific projects. They enter the date, client, action code, hours worked and a description. The second table is a summary table for the clients, with the client code along with a sum of total hours worked for that project (There are different projects for a single client). I was wondering if anyone knew a macro that would make it so when the worker entered in a specific client code on the first table, that same code would be entered onto the second table. And once a code is entered once on the second table it wont be entered again on the second table even though it might be entered again on the first table. If someone knows a macro or formula that could help me please let me know.

    Thanks!
    Last edited by pdowney; 08-05-2014 at 11:31 AM.
    Reply With QuoteRegister To Reply
  2. 08-04-2014, 07:24 PM #2
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro Help: Auto Populate Summary Table

    You need a custom routine and in order to make one for you we need a sample spreadsheet - sanitized and smallish
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
    Reply With QuoteRegister To Reply
  3. 08-05-2014, 11:33 AM #3
    pdowney
    pdowney is offline
    Registered User
    Join Date
    08-04-2014
    Location
    Washington, DC
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Macro Help: Auto Populate Summary Table

    Contractor Hours Template.xlsx

    I have attached the spreadsheet. I have manually entered one of the entries into the summary sheet but I would like it so that when that is entered into table 1 that it would automatically appear in table 2.
    Reply With QuoteRegister To Reply
  4. 08-05-2014, 12:58 PM #4
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Macro Help: Auto Populate Summary Table

    Hi pdowney

    Welcome to the Forum!!

    In opening your attachment I get "...Unreadable Content..."

    You might wish to upload a new Sample File.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
    Reply With QuoteRegister To Reply
  5. 08-05-2014, 01:48 PM #5
    pdowney
    pdowney is offline
    Registered User
    Join Date
    08-04-2014
    Location
    Washington, DC
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Macro Help: Auto Populate Summary Table

    Contractor Hours Template sample.xlsm

    Lets see if this one works. Thanks for all the help.
    Reply With QuoteRegister To Reply
  6. 08-05-2014, 05:10 PM #6
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Macro Help: Auto Populate Summary Table

    Hi pdowney

    Clarification please...

    If the Client Code already exists in the Summary Table you indicate it should not be added again. However, what if the Action Code is different? Should that existing Client Code be added with the new Action Code?

    For example, assume 8300.0016 exists with an Action Code of TB and a new entry is created for 8300.0016 with an Action Code of TNB. Should the Summary Table now have two entries for 8300.0016...

    8300.0016 TB
    8300.0016 TNB

    Edit:

    AHHH...TB may stand for Billable and TNB may stand for Not Billable...if so, what does TTN stand for? ...Negotiable???
    Last edited by jaslake; 08-05-2014 at 05:27 PM.
    Reply With QuoteRegister To Reply
  7. 08-06-2014, 08:34 AM #7
    pdowney
    pdowney is offline
    Registered User
    Join Date
    08-04-2014
    Location
    Washington, DC
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Macro Help: Auto Populate Summary Table

    Jaslake,

    Yes you example is exactly what I am looking for. I need it so that it would show up for all three action codes if they were all three entered. Also there might be a case that would have another entry as 8300.0017 instead of 8300.0016. So in that case I would need to have 8300.0017 show up again on the summary table as well.

    And on the edit note your correct it stands for time negotiable.
    Reply With QuoteRegister To Reply
  8. 08-06-2014, 12:35 PM #8
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Macro Help: Auto Populate Summary Table

    Hi pdowney

    Try this Code in the attached...the Code fires when a change is made in Column D, Action Code
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 08-06-2014, 01:02 PM #9
    pdowney
    pdowney is offline
    Registered User
    Join Date
    08-04-2014
    Location
    Washington, DC
    MS-Off Ver
    Office 2010
    Posts
    8

    Re: Macro Help: Auto Populate Summary Table

    Jaslake,

    Thank you so much that works perfectly. However I also had a macro that automatically puts the dates in chronological order no matter where they were entered in table 1. this is the code that worked for me:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column <> 2 Then Exit Sub
    If Target.Count > 2 Then Exit Sub

    Range("B12").Sort Key1:=Range("B12"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    But now I cannot figure out how to get both macros to work together.

    Any suggestions?

    Thanks!

    End Sub
    Reply With QuoteRegister To Reply
  10. 08-06-2014, 04:21 PM #10
    jaslake
    jaslake is offline
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Macro Help: Auto Populate Summary Table

    Hi pdowney

    Please use Code Tags around any Code you post...it's a Forum requirement. Edit your Post #9, highlight the Code and click the # sign.

    After doing so, try this Code. I had to redefine Table2 to not include Column A.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Auto populate from sheet to summary as next on a list and date stamp
    By Pattio in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-11-2014, 02:10 PM
  2. Macro to update and populate a summary table with data from multiple and new sheets added.
    By negomi in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-13-2014, 05:15 AM
  3. [SOLVED] Auto-populate summary sheet... with dynamic info.
    By jhren in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-30-2013, 08:33 AM
  4. Help with macro to auto populate summary sheet Excel 2007
    By acook in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-24-2011, 03:01 PM
  5. I want to conditionally auto-populate a summary worksheet
    By trigger in forum Excel General
    Replies: 0
    Last Post: 01-13-2006, 11:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1