imports, dates, filter, sums
I am importing a CSV file when imported has column for date (mm/dd/yyyy), employee. I need to do a report where it will total up each employees hours for the month, then take the pay rate and multiply the hours to give me the total price for each individual and then add up the amounts to give a total labor cost for the month. Every month I will be importing a report and I can either just import all the information for the year or I can import for just the month. I am not sure how I can go about doing this, is this something I would just use a macro for each step of the process? or is there a way to group (without knowing how big each group is going to be) I am trying to set this up so that it will be automatic so that my boss can also use the form if I am not in the office.
I hope I made sense.
Thank you in advance for any help.
hi,
Welcome to the Forum
Without considering too much of the detail I think a pivot table may be the way to go. I'd recommend staying away from macros until* you have a working prototype & a clear list of required steps that you complete manually. Once you have this clarity it is much easier for 1) you to explain your needs & 2) for us to provide a more suitable solution based on your needs & prototype/template design.
NOTE: I'm not saying "don't use a macro" (I love them!), I'm just requesting you are have a very sound understanding of what you need before you attempt to automate a process - we wouldn't want it to error on your first day of holiday due to some data quirk or an oversight!
Here are some links which provide an introduction to Pivot Tables. Have a look through & see if you think that you could use a pivot table approach.
http://peltiertech.com/Excel/Pivots/pivottables.htm
http://www.contextures.com/xlpivot01.html
If possible, once you have a working prototype can you please upload a sample workbook with specific questions?
We can then make suggestions or provide links for enhancements/automation.
hth
Rob
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
I have tried to put the pivot table in using those directions but they are not working for me. When I put the information in a pivot table I cannot seem to pull the information that I want. I have attached a sample of the information.
When I import the timesheet information it does not have the individuals hourly rate. So right now I have a section of the sheet setup to tally up the total hours for an individual and then multiply that by the hourly rate. So I have to do an import of the monthly reports and override the last months information. I want to be able to keep all the information for the entire year and then parse out each months hours times each individuals rate of pay and then have the sheet place that in the corresponding months cell for the total. I use this informaiton in nother formula on another sheet in my workbook.
I thank you for the help.
hi,
It's very difficult to decide the best way to help with such a limited example, can you please upload a csv file of dummy data?
Make sure that there is absolutely nothing confidential in the file, but that it does have the same layout as your real file.
Will everyone who uses this report be using excel 2007 (as the file is saved as a .xlsx*) or will some people be using Excel 2003?
*Do you need to update your profile to state excel 2007, or do you have both versions?
Rob
Rob Brockett
Kiwi in the UK
Always learning & the best way to learn is to experience...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks