+ Reply to Thread
Results 1 to 8 of 8

Rota help - Vlookup???

  1. #1
    Registered User
    Join Date
    10-09-2013
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Rota help - Vlookup???

    I have a spreadsheet (attached) where I have a rota for all colleagues by week and then a daily tab which will separate out the names into their department.

    I need to have a formula on the 2nd tab (in the highlighted red box) to look up the next person who works in that department that is due in from the rota on the other tab and return the name.

    Hope that makes sense and this is an easy fix that I just cannot see

    Thanks again for you help
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Rota help - Vlookup???

    Hello
    I've placed some formulas in the attached sheet to look up the names for each Department. I've also created a lookup table to work with a Vlookup in the Hours column to make that formula shorter and more easily editable.

    Hope I've understood the requirement and it's of some help.
    DBY
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-09-2013
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Rota help - Vlookup???

    thats great, thanks for your help

    I am going to create another tab for mondays deployment. how can I make the formula only look at a certain day and return the names accordingly?

  4. #4
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Rota help - Vlookup???

    Hello
    Glad it's of some use. So for example, does Name 1, shift 18-06, dept. Amb need to be returned for Sunday, Friday and Saturdays only?

  5. #5
    Registered User
    Join Date
    10-09-2013
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Rota help - Vlookup???

    yes thats right. Basically I have set up a rota for all colleague which runs forever.
    These daily sheets just need to look up each persons name and day they will attend and then filter through to that tab.
    Find attached spreadsheet which has all tabs and names etc
    Thanks again for the help
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Rota help - Vlookup???

    I might need a bit of time to digest this and think of the best way to go. You are going to end up with a lot of very long formulas, so I'm not sure how this will affect the performance of the spreadsheet. If I could get back to you tomorrow I might have a few suggestions. Just a couple of questions. Could I add some hidden helper columns to aid the calculations and perhaps a couple of lookup tables? And can you confirm that you are using Excel 2003 and not anything later?

    If anyone else comes in to help in the meantime be sure to go with them if it helps.

    DBY

  7. #7
    Registered User
    Join Date
    10-09-2013
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Rota help - Vlookup???

    Yes 2003, no go ahead any columns or whatever you need just thankful of the help!

  8. #8
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Rota help - Vlookup???

    There's a lot going on in your workbook, so I think it best to use helper columns and cells wherever possible. I'm not sure exactly how you define what is a night shift and day shift, so I've assumed it's based on the the shift pattern with those times straddling midnight being night shifts. See the attached amended example file as to how this might work. I've completed examples for Sunday Nights and Friday Days. The yellow highlighted columns can be hidden if need be. You should be able to extrapolate the general idea to your actual workbook.

    DBY
    Attached Files Attached Files

+ 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. Rota Help
    By MikeFord in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-14-2012, 08:16 AM
  2. Rota's
    By noggins in forum Excel General
    Replies: 1
    Last Post: 10-19-2011, 05:13 PM
  3. Rota
    By -Doubleoseven- in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 03-14-2011, 05:34 PM
  4. Rota
    By noviceben in forum Excel General
    Replies: 1
    Last Post: 04-03-2007, 03:45 PM
  5. Rota
    By chris.howes in forum Excel General
    Replies: 1
    Last Post: 07-24-2006, 07:35 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