+ Reply to Thread
Results 1 to 6 of 6

Tracking hires and costs - Help needed

  1. #1
    Registered User
    Join Date
    10-20-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    365
    Posts
    3

    Question Tracking hires and costs - Help needed

    Hi,

    I'm new here and was hoping you can help me deciding the best way to design a template in Excel.

    I need to track resources and spent by department and by project. The idea is that in this table I register every resource, when they start, when they finish, their salary and what project they have been working on. I'm keen to keep it simple so a number of us can use it on a daily basis.

    The objective is to be able to have 2 charts where I can see how resources have decreased or increased in the last few months and how do we expect it to move in the future (as I'll have in the table future hires). The second table would be quite similar and but instead of number of resources it would be the monthly cost.

    I'm not sure where to start, I thought of a pivot chart as it should allow me to filter by project or department but struggling to have the number of resources increasing based on the month of the starting date and the same for when they finish. Any guidance?

    Thanks!
    Ezequiel

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Tracking hires and costs - Help needed

    Hi and welcome to the forum

    The most important piece of advice I can offer is to capture all your data in a regular 2 dimensional range on a single sheet, where the column labels are unique types of information and each row is a new record. By unique types I don't mean unique values. For instance often we find that people will capture stuff like dates, project numbers, staff names..etc with each value in a separate column. That might at first glance seem simple and obvious but will complicate any analysis. Capturing Data and Reporting data are two different operations. Reporting is made easier if the data is in a normalised table/range.

    So for example for stuff like dates, project numbers, staff names, you would have a single column for each type of information and record the values i.e. dates, names...etc in different rows in the relevant column.

    In addition don't fall into the trap of using different sheets for things like months, weeks, projects ...etc.

    From your description since you mention start and end dates for projects the thing to decide is should your database contain two columns, one for the start date and one for the end date, or would you have one column for "Start/End" which would contain the words 'Start' or 'End' on each row and another column for the Date.

    One might have an advantage over the other. To comment with a little more authority we'd need to see an example in a workbook of the sorts of things you want to capture and then a report layout of what you'd expect to see. A Pivot Table may be the best way of reporting.

    You mention sharing the workbook. In previous versions of Excel and although Excel had some sharing functionality it generally got in the way of other excel functionality, particularly if macros were involved and most of us avoided it like the plague. If all your users have Excel in the Office 365 environment then new sharing functionality is available. Microsoft call it co-authoring.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-20-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    365
    Posts
    3

    Re: Tracking hires and costs - Help needed

    Hi Richard,

    Thanks a mill for your quick response.

    I have attached the first draft of the workbook, it's very basic but hopefully it helps with what I'm trying to do. The second tab is just for you to have an idea of how the graph would look like, I would prefer not having any additional tables, keen to keep everything in a single table so it's easy for people to update it.

    At the moment it's all quite manual for the reporting which is the biggest problem. I thought of using some IFCOUNTS but felt it was just too complex for something that should be simple enough (I think!) so maybe I'm doing something wrong or there's a better way of doing it.

    Regarding sharing the workbook as you suggested the plan is to use the co-authoring. We'll also try to keep access quite limited given the sensitivity of the information and we would only update the file once a day or so.

    Thanks again for all your help!

    Ezequiel
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Tracking hires and costs - Help needed

    As to the table and graph on the table sheet I feel that Richard is suggesting that the table be arranged row over row as modeled on Sheet1. Notice that it will produce the same graph.
    As to the monthly cost table and graph. When the data on the Input sheet is converted to a table and then Excel recommends a graph, it will produce a pivot table, which may modified as modeled on Sheet2, and pivot chart.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    10-20-2020
    Location
    Dublin, Ireland
    MS-Off Ver
    365
    Posts
    3

    Re: Tracking hires and costs - Help needed

    Hi JeteMC, thank so much for your help and apologies in the delay. This has worked like a charm!

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Tracking hires and costs - Help needed

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sum Total Costs for Each Month Based between the Phase Dates Formula Needed
    By garrett.grillo in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-18-2017, 09:16 PM
  2. Help Needed With Tracking Inventory
    By travis.cook21 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2017, 11:09 AM
  3. Moving 'Total' Rows Below a List of Costs Needed
    By david.w. in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-06-2014, 04:08 PM
  4. help needed on Tracking excel
    By k_paresh in forum Excel General
    Replies: 4
    Last Post: 08-25-2013, 12:56 PM
  5. Excel 2007 : Help needed 'tracking changes'
    By janalyst in forum Excel General
    Replies: 0
    Last Post: 08-26-2011, 06:15 AM
  6. a template for tracking house building costs
    By kevy in forum Excel General
    Replies: 0
    Last Post: 08-15-2005, 05:05 PM
  7. Replies: 2
    Last Post: 04-05-2005, 09:06 AM

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