+ Reply to Thread
Results 1 to 17 of 17

Budget Remaining Database with multiple Projects split by date

  1. #1
    Registered User
    Join Date
    11-09-2012
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Budget Remaining Database with multiple Projects split by date

    Hello,

    So this is my first time posting on this forum, as I have reached a point where I have absolutely no idea what to do with my spreadsheet to get what I want. I hope that someone will be able to help me. (please let me know if I don't explain my problem well.)

    Alright, so I am creating a workbook that has multiple tabs for each individual project and then bring in the total salaried cost to a consolidated tab. For each project we have different rates that we charge per employee and this differs per project ( I have these rates in a column that is multiplied by the amount of hours to determine total salaried cost). I have created a vlookup to bring in the amount of hours worked per project. (this was from a pivot table that has projects and person, with hours as the value). The problem I have ran into is the fact that the employees pay rates have changed over the past 3-4 years, (all employees pay rates change about every 6 months.) Is there a way that I can still vlookup the amount of hours however separate it out by date and then multiply by the rates I have? Currently I do have a data file that I create the pivot table from and it does have all the data necessary, employee name, project number and dates. Currently the dates aren't in the pivot table. Is there anyway to bring in the dates to the pivot table and split the hours based on a selected date range from this? What would the pivot table have to be and what would the formula be? Or is there a better way to do this? Any help would be greatly appreciated!!!

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Budget Remaining Database with multiple Projects split by date

    I bet you get better help if you show us an Excel example of your data, without confidential information.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    11-09-2012
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Budget Remaining Database with multiple Projects split by date

    The Data file that I have is formatted like this

    And I also have a pivot table of the data comparing project and employee and the value is hours worked

    The data contained in this is just an example, the actual data file that I am dealing with contains a lot more rows.

    So the end result I want is to the amount of hours by employee, project and within a certain date range. Please let me know if you would need any more information.

    help budget.xlsx
    Last edited by Dhanaway02; 11-09-2012 at 01:26 PM.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Budget Remaining Database with multiple Projects split by date

    With index/match?

    see the attached file.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-09-2012
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Budget Remaining Database with multiple Projects split by date

    Is there anyway of doing this without manipulating the data file?

    And also I want to bring in hours within a certain date range for example the salaries changed half way thru 2009. I want to pull that into another tab and then multiply it by salary rates. Whereas the second half of 2009 I would want to multiply it by a different set of salary rates.

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Budget Remaining Database with multiple Projects split by date

    1) I don't know where you want the answers.

    2) change the data of 2009 in 2 columns and then you also can use index/match
    Last edited by oeldere; 11-09-2012 at 04:22 PM.

  7. #7
    Registered User
    Join Date
    11-09-2012
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Budget Remaining Database with multiple Projects split by date

    Would it be easier if I sent the workbook with the multiple tabs? and I highlight where I want the hours to be brought in?

  8. #8
    Registered User
    Join Date
    11-09-2012
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Budget Remaining Database with multiple Projects split by date

    After re-reading my thread I realized that it would be alot easier if I posted an excel document showing what I EXACTLY need done. Therefore I have manipulated my document so give a better example of what I want done. So I need to bring in the hours from the data table based off of the date period specified in the project table either (project 1234 or 4444). I need to bring in these hours based off of project, employee, and split them up for the specified time period and input the hours (I highlighted these columns in yellow, where the formula should be.) I do not want to manipulate the data file. I would then input the total salary into the CONSOL tab, ( I forgot to update the project numbers)

    Please let me know if anyone has any suggestions! THANKS!!budget need HELP.xlsx

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Budget Remaining Database with multiple Projects split by date

    It is not what you asked for, but please take the time to read the information below and take a look at the attached file.


    In you're case i advice you to learn pivot table to get the desired result.

    I add columns to your worksheet DATA (column X and further)

    I add an worksheet salary (for the data, which can change).

    I uses index / match to find the right salary.

    Then I multiply hour * salary.

    Then I made an table of the data.

    Excel 2007 => insert => table.

    Then I made an pivot table on a new worksheet (named pivot table).

    I made 2 pivot table of the same (table).

    In the first one you can see the hours and cost per employee.

    In the second one you can see the cost per project (per month and year).

    You can also use other options, but this is just to show you, what is possible.

    Do you have questions or comments on this item, just ask or comment.
    Attached Files Attached Files
    Last edited by oeldere; 11-10-2012 at 04:32 AM. Reason: changed text

  10. #10
    Registered User
    Join Date
    11-09-2012
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Budget Remaining Database with multiple Projects split by date

    Thanks for your help, I do believe what you just showed me will work. However I cant get the data to pull the correct salary per date range. I have provided a file that shows the problem I am running to.

    Also, would you mind telling me how the formula functions, like how it tells it to pull the correct rate per date range. Thanks so much!!!

    The correct salary is not being pulled into column AA for Hours (D) tab.

    11_3 and before data_wip HELP.xlsx
    Last edited by Dhanaway02; 11-13-2012 at 11:41 AM.

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Budget Remaining Database with multiple Projects split by date

    aa5 =
    Please Login or Register  to view this content.
    you get N/A in your cell.

    Above you find the DUTCH formula (in englisch is this index / match).

    In the salary table employee 4 is in a14 => a14 isn't in the range (see the red cell).

    Change the range and the data wil be found.

    See F1 (helpfunction) for index / match for explaination.

    Index (which is the table the data are in).

    Match (seek the employee in column A of the worksheet Salary Table).

    Match (seek the date in row 3 of the worksheet Salary Table).

    Find the break even point (cross point) and show that data.

  12. #12
    Registered User
    Join Date
    11-09-2012
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Budget Remaining Database with multiple Projects split by date

    For some reason this is still not working! ugggggh.. Can you check the attachment I had put on my last post? It shows the example of the wrong salary being pulled, and I cannot figure out why it is pulling the wrong number. If you look at cell AA2, the number that should be pulled is 60, whereas the number that is pulled is 66 which is wrong!

    Sorry I was just making sure my data didnt contain confidential information thats why the previous equation didnt include the entire range.


    Thanks again for your continuing support!
    Last edited by Dhanaway02; 11-13-2012 at 05:09 PM.

  13. #13
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Budget Remaining Database with multiple Projects split by date

    See the red cell in the attached file.

    If a employee is not available in you list on the salary sheet you get an error #N/A.

    So i added this employee and see the result in the red cell.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    11-09-2012
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Budget Remaining Database with multiple Projects split by date

    Thanks but the index formula is still bringing back the wrong salary.

    Look at cell AA2, the salary it has is $66 (which is WRONG). As of 06-2009 the salary that should be brought back is $72.60 So I am thinking there has to be something wrong with this equation

    =INDEX('Salary table'!$A$3:$E$20,MATCH('Hours(D)'!B2,'Salary table'!$A$3:$A$20,0),MATCH($Z2,'Salary table'!$A$3:$E$3,1))

  15. #15
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Budget Remaining Database with multiple Projects split by date

    See the yellow cells.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    11-09-2012
    Location
    Philadelphia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Budget Remaining Database with multiple Projects split by date

    Thats awesome thanks so much. everything is finally working


    I think I am going to have to scap the entire spreadsheet though. We have so many different projects around 20. For the majority of the projects we have the same salary changes throughout the years. However for a few projects around 4/5 these are special contracts we are locked in and they all have different salary rates. At the end of the day I dont think I have enough knowledge of Excel to be able to produce a spreadsheet that will work like I want it to.

    Thanks for helping me though!

  17. #17
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,953

    Re: Budget Remaining Database with multiple Projects split by date

    Thanks for the reply.

    Will you mark your question as solved?

+ 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