+ Reply to Thread
Results 1 to 6 of 6

Help with a formula for populating a rota with names

  1. #1
    Registered User
    Join Date
    08-09-2017
    Location
    London
    MS-Off Ver
    2016
    Posts
    2

    Help with a formula for populating a rota with names

    Hi there,

    I'm looking for some help with the attached rota.

    Basically, what i'm looking for is help with a formula to populate a rota and make things a bit easier to manage.

    Say for example on the 'Rota' sheet, if I was looking at B9, I would like to work out how i could get the spreadsheet to look on the 'Hours' spreadsheet for the a 2-10.30 shift in the B column of that sheet, and then when it finds 2-10.30, it shows the persons name from the A column on the 'Rota' Sheet.

    So for example on the document I attached, on the hours sheet, it would look down the whole B column, find 2-10.30 in B11, and then would display the value of A11, in this case 'Bill'.

    Does that make sense? I'm fairly new to excel, i'm been trying things like VLOOKUP and MATCH but can't seem to get anything to work.

    Any help would be most appreciated, although i'm not even sure if what i'm asking is possible!

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Help with a formula for populating a rota with names

    VLookup cannot look left which is the issue yo uare having.

    Put this in B9

    =INDEX(Hours!$A$3:$A$62,MATCH(C9,Hours!$B$3:$B$62,0))

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,866

    Re: Help with a formula for populating a rota with names

    I think we look in column C and put answer in B based on your file

    in B3

    =IFERROR(INDEX(Hours!$A$3:$A$58,MATCH(Rota!$C3,INDEX(Hours!$B$3:$H$58,0,MATCH(Rota!B$2,Hours!$B$1:$H$1,0)),0)),"")


    Copy down

    Highlighted references need to be amended for other days/times

    Attached saved as xlsx file
    Attached Files Attached Files

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,587

    Re: Help with a formula for populating a rota with names

    b9=IFERROR(INDEX(Hours!$A:$A,MATCH(C9,INDEX(Hours!$1:$65536,,MATCH(B$2,Hours!$1:$1,0)),0)),"")

    The cell format is in Text, Change the cell format to general
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Registered User
    Join Date
    08-09-2017
    Location
    London
    MS-Off Ver
    2016
    Posts
    2

    Re: Help with a formula for populating a rota with names

    Hi,

    Thanks so much for all the help, it's hugely appreciated.

    Only other issue i'm now facing is that when there are multiple people doing the same shift on a single day.

    For example two people on Tuesday morning working 7.30-2.30, it just pulls through the box I input first for both shifts.

    Is there any way around this?

    Thanks,

    Alex

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,866

    Re: Help with a formula for populating a rota with names

    Try

    In B3

    =IFERROR(INDEX(Hours!$A$3:$A$62,SMALL(IF(Hours!$B$3:$B$62=$C3,ROW(Hours!$A$3:$A$62)-ROW($A$3)+1,""),COUNTIFS($C$3:C3,C3))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Copy down to row 6

    Select B3:B6, copy and paste to B9:B12

    All ranges need adjusting for each day.
    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. Auto-populating tab names help
    By Nemery in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-20-2017, 04:58 AM
  2. Dropping names through a rota
    By Jonboi307 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-17-2017, 06:16 PM
  3. Replies: 14
    Last Post: 05-23-2015, 06:26 AM
  4. Replies: 0
    Last Post: 04-07-2015, 11:39 PM
  5. Replies: 3
    Last Post: 11-22-2013, 05:06 PM
  6. [SOLVED] Populating names from another sheet
    By jloz in forum Excel General
    Replies: 1
    Last Post: 04-19-2013, 04:40 PM
  7. Work Rota:randomises the names
    By Belter in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-04-2006, 11:25 PM

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