If you have not received any solution yet, I would like to see the Sample data worksheet.
-----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of SLF Sent: Monday, May 31, 2010 10:45 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Automatic Detail Summary Hello all! I am building a complex spreadsheet to track 2 weeks of payroll and projects. It needs to be a completely automatic document that can calculate everything for itself. All that will be entered is the hours. So here's my problem. I have a simple master payroll table on one tab that looks like this: Project # Employee 1/1/2010 1/2/2010 1/3/2010 1/4/2010 1/5/2010 1111 Kevin 5 0 5 4 2 2222 Julie 1 5 4 6 5 1111 Logan 2 0 4 2 0 1111 Ryan 1 0 4 3 2 3333 John 0 4 4 8 2 My boss wants to have a detailed summary sheet for each project number. He wants to only see the pertinent hours for said project on that sheet. So he wants to see only this in the detailed sheet for project 1111: Project # Employee 1/1/2010 1/3/2010 1/4/2010 1/5/2010 1111 Kevin 5 5 4 2 1111 Logan 2 4 2 0 1111 Ryan 1 4 3 2 As far as I figure, I CANNOT do the following: **Use a filter. It would be easy to filter the data in place, but to get the data to the detailed project sheet, I would have to copy and paste. It has to happen automatically. There will be a person enetering hours everyday, but we have to assume that they are not capable of anything else. **Use a vlookup. As far as I am aware, a vlookup can't return a list of mulitple occurences of the same value. My ideas: **Create a pivot table in the background, then somehow use a getpivot formula to pull out only the relevant data? Can I somehow use getpivot to return a whole section of a pivot table instead of one cell? I know I can easily build the info in a pivot table...but how do i make it automatic? how do I get this pivoted data to simply show up in the other sheet? **Use an index lookup to cross reference the employee names and dates and return the pertinent hours. However, this would require that I already have the list of dates relevant to the project. (maybe from a pivot table again?) Thanks for reading! Hope you have some ideas for me! -- ---------------------------------------------------------------------------- ------ Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
