+ Reply to Thread
Results 1 to 16 of 16

Using excel to record employee holiday

  1. #1
    Registered User
    Join Date
    05-24-2013
    Location
    Hampshire
    MS-Off Ver
    Office 2013
    Posts
    49

    Using excel to record employee holiday

    Hello All

    I know most of the basic uses of excel, probably heading into the intermediate category of user.

    I didnt do a search, because I dont know what exactly I need to search for to gain my result I am looking for, so sorry in advance if it turns out to have been covered elsewhere.

    What I have in the attachment is a spreadsheet recording holiday for drivers who work for me. In the 'Drivers Tab', the layout is split as I will be using this spreadsheet in Excel 2003 which runs out of columns, as apposed to me using 2007 to make it. In the 'Drivers' Tab, I have a list of Drivers, their holiday entitlement for the year, with formulas working out how much is booked and unbooked. Then I have conditional formatting as you can see in the columns, green cells to highlight the hours booked, and red columns if there are more than 2 drivers booking the same day off.

    In the 'Individual Colleague Printout', this is a slimed down version which really is for reference to the colleagues if they request a printout of their annual holiday. The main black box up the top uses Data Validation drop down menu to get a list of the drivers from the 'Drivers' tab. And then basically in the actual content itself, uses vlookup to get the results if the colleague has booked that day off. It will be easy to see what I have done when you open up the spreadsheet itself.

    Now, onto the main point of me posting this thread. Basically when a colleague hands a holiday form into me, I input the data into the 'Drivers' tab. What I was wondering, would it be possible for me to enter the data into the Individual Colleague Printout tab itself, and then automatically publish into the Drivers tab? And if I select another driver from the black drop down list, for me to do the same and it keep presenting the data for the driver selected, aswell as inputting into the drivers tab?

    Any help will be fantastic! Many thanks in advance!

    Ash
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,512

    Re: Using excel to record employee holiday

    Hi, Ash!

    At the moment, your individual sheet feeds from the drivers' tab, which works fine. Are you wanting the individual sheet to continue to draw some information from the drivers' tab, whilst adding just the hours they want via the individual tab? What is the significance of all of the numbers to the right of the individual's form? (EDIT: Just worked this out!)
    Last edited by AliGW; 02-21-2014 at 05:45 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    05-24-2013
    Location
    Hampshire
    MS-Off Ver
    Office 2013
    Posts
    49

    Re: Using excel to record employee holiday

    Hello, thanks for your response!

    Yes basically I am just using vlookup of the drivers name and searching within an array on the Drivers tab.

    Are you wanting the individual sheet to continue to draw some information from the drivers' tab, whilst adding just the hours they want via the individual tab?
    Yes, thats correct. Basically when a colleague hands a holiday form in, it is in the same layout as the Individual tab. So at the moment I have to look at the piece of paper, and match up what they have written and then enter it into the Drivers tab table, which is time consuming and sometimes confusing when looking at so many cells. What I would like to be able to do, is just enter all the data into the Individual tab.

    The only reason I would need to keep the main drivers tab, is when I do the weekly schedules, so its easy for me to look at tell who is on holiday for certain days!

    Hope the above makes sense!

    Many Thanks

    Ash

    EDIT: Just incase someone else takes a look aswell and cannot work the following out:

    What is the significance of all of the numbers to the right of the individual's form? (EDIT: Just worked this out!)
    They are used within the vlookup in the cells on the individual holiday form. Basically it just saves me writing out individual formula for every cell, allowing me just to copy and paste which is much quicker

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,512

    Re: Using excel to record employee holiday

    Having had a good look at this, I think you would need one tab per driver. These would be hidden, but would feed the hours into the drivers' tab (the drivers' tab would need to be set up for each of the driver rows to read from the correct driver tab). You could then use the drop-down in your individual proforma to select the driver and the relevant sheet using the INDIRECT function. To all intents and purposes, the spreadsheet would look exactly the same as it does now. You would enter hours via the proforma.

  5. #5
    Registered User
    Join Date
    05-24-2013
    Location
    Hampshire
    MS-Off Ver
    Office 2013
    Posts
    49

    Re: Using excel to record employee holiday

    Blimey, sounds like exactly what I need, but I have no idea how to go about this, maybe ill just leave it how it is :D

    Thanks for your help anyway!

    Ash

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,512

    Re: Using excel to record employee holiday

    No - hang on in there! I have an idea. Give me a little while and I'll see if I can make it work!

  7. #7
    Registered User
    Join Date
    05-24-2013
    Location
    Hampshire
    MS-Off Ver
    Office 2013
    Posts
    49

    Re: Using excel to record employee holiday

    Brilliant, look forward to seeing what you come up with! Very greatful for your help!

    Ash

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,512

    Re: Using excel to record employee holiday

    Could be a while ... I'll do my best! Will let you know how I get on.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,512

    Re: Using excel to record employee holiday

    Hi again, Ash!

    I have been mulling this over, and there are two ways you could go, as far as I can see: you could have a tab for each driver and enter the hours booked directly onto their form, which would then feed into the composite drivers' tab and could be printed out as a paper form, or you could look into adding a data entry sheet to the file, that would feed directly into the drivers' tab and from there into the proforma you already have for printing. I'm afraid I don't have time to look into the data entry form for you, but I think it is probably the way you need to go. Hope this helps.

  10. #10
    Registered User
    Join Date
    05-24-2013
    Location
    Hampshire
    MS-Off Ver
    Office 2013
    Posts
    49

    Re: Using excel to record employee holiday

    Hello!

    I will have to look into something, as I said it isnt anything too major which I cannot cope without! I might look into a sort of macro system perhaps, we will see!

    Just want to thank you for all your time you've put into help me today!

    Ash

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,512

    Re: Using excel to record employee holiday

    You're welcome.

  12. #12
    Registered User
    Join Date
    05-24-2013
    Location
    Hampshire
    MS-Off Ver
    Office 2013
    Posts
    49

    Re: Using excel to record employee holiday

    BUMP. Was wondering if anyone else could possibly take a look at this?

    Many Thanks

  13. #13
    Registered User
    Join Date
    05-24-2013
    Location
    Hampshire
    MS-Off Ver
    Office 2013
    Posts
    49

    Re: Using excel to record employee holiday

    Anyone have any idea, pretty please?

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,512

    Re: Using excel to record employee holiday

    The most irritating thing about this forum, as far as I am concerned (and I've not been posting for very long here), is the fact that most of the time, once one person has tried to help in a thread, others don't bother to look at it. I'd risk incurring the (a bit over-zealous!) moderators' wrath by starting a new thread with a much more explicit title, but provide a link back to this thread in it. Hopefully this will get the ball rolling again for you.

  15. #15
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,615

    Re: Using excel to record employee holiday

    Here is an alternative using date entry, pivot table and SUMIFS.

    Your problem is combining data entry with presentation. If you separate them then number of columns is not a restriction.
    Although as you already knew the restriction I not sure why you did not use columns for drivers and rows for dates.

    Any way, the example file has data entry on Sheet1. I've used a table to help the pivot table and formula keep sync with new records, although the PT will still require a refresh after new records are added.
    The table contains a single driver entry for their entitlement. The for each day the booked hours are stored.

    Sheet2 contains the pivot table so you can see all drivers, their entitlement, booked and unbooked hours.

    On ICP sheet I have used SUMIFS formula to fill in the annual sheet for selected driver.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  16. #16
    Registered User
    Join Date
    05-24-2013
    Location
    Hampshire
    MS-Off Ver
    Office 2013
    Posts
    49

    Re: Using excel to record employee holiday

    Not exactly what I was after, however thats a massive step in the right direction for me! :D

    Thank you very much for your time and effort Andy!

    Ash

+ 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. Employee Attendance Record
    By PurpleMe in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-23-2013, 02:15 AM
  2. [SOLVED] Creating a attendance record for office employee?
    By benn371 in forum Excel General
    Replies: 3
    Last Post: 06-24-2013, 10:53 AM
  3. Excel 2007 : Employee Monthly Attendence Record
    By Lou54 in forum Excel General
    Replies: 4
    Last Post: 05-16-2012, 03:54 PM
  4. Employee Holiday Tracker
    By Benjamin2008 in forum Excel General
    Replies: 4
    Last Post: 02-05-2010, 11:07 AM
  5. Staff holiday Records - Dynamic Record?
    By alages in forum Excel General
    Replies: 1
    Last Post: 03-21-2006, 11:08 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