I think, using advance filter to filter data and then making use of it would be useful
Harry On May 31, 10:14 am, SLF <[email protected]> wrote: > 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 [email protected] <><><><><><><><><><><><><><><><><><><><><><> 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
