+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : imports, dates, filter, sums

  1. #1
    Registered User
    Join Date
    10-27-2010
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Cool 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.

  2. #2
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: imports, dates, filter, sums

    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...

  3. #3
    Registered User
    Join Date
    10-27-2010
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: imports, dates, filter, sums

    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.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: imports, dates, filter, sums

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1