+ Reply to Thread
Results 1 to 11 of 11

Look up function

  1. #1
    Registered User
    Join Date
    08-20-2010
    Location
    scotland
    MS-Off Ver
    Excel 2003
    Posts
    22

    Look up function

    Hi,

    Looking for a bit of help with my shift rota spreadsheet. On the Schedule part of the sheet I choose the names of people doing the shifts and there hours are auto calculated on employee hours page.

    However what im looking to do is have an auto look up on Shifts by name page where it looks to the schedule and pulls over the data to the corresponding name, ie shift and time.

    I have attached the spreadsheet to this thread any help is appreciated.

    Thanks in adavance
    Attached Files Attached Files
    Last edited by NBVC; 05-16-2011 at 01:55 PM.

  2. #2
    Forum Contributor
    Join Date
    07-07-2005
    Location
    England
    MS-Off Ver
    Office 2019 Enterprise
    Posts
    462

    Re: Look up function

    could you resend the spreadsheet but this time with details filled in (manually) for A Coull and A Day on the Shifts By Name sheet.

    this will help get a grasp of exactly what you expect to see on this sheet

    John

  3. #3
    Registered User
    Join Date
    08-20-2010
    Location
    scotland
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Look up function

    Thanks for the reply, I have attached the file again with the manual additions.

    Basically what I want it to do is look at the schedule for each individual and bring over into shifts by name the site there on under which day and time there working. The reason for this is the person could be at various sites throughout the week.

    Hope this helps explain things
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Look up function

    Have a review of the attached to see if it meets your requirements.

    In B2 of Shifts by Name sheet:

    Please Login or Register  to view this content.
    In B3:

    Please Login or Register  to view this content.
    This formula is an array formula and must be confirmed with CTRL+SHIFT+ENTER keys not just ENTER

    Because your data in Schedule sheet is in alternate columns, you can't just copy across. After you copy these to cells across, you will have to adjust the B$1:B$200 range to D$1:D$200 and so on.

    Then you can copy B2:H3 cells and paste to each white cell in column B and they should give correct results.

    (Note: This all assumes the person appears only once in a give day... otherwise it will take their last entries only)>Attachment 108254
    Last edited by NBVC; 05-13-2011 at 09:34 AM. Reason: Corrections in formula typed... thanks arthurbr
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Look up function

    Small typo I think

    =IF(ISNUMBER(MATCH($A2,Schedule!B$1:B$200,0)),LOOKUP(2,1/(Schedule!$A$1:INDEX(Schedule!$A$1:$A200,MATcH($A2,Schedule!B$1:B$200,0))<>""),Schedule!$A:$A),"")
    Last edited by arthurbr; 05-13-2011 at 09:09 AM.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Look up function

    Thanks Arthurbr.

    That's because of the fact that the data sheet is in every other column, so I used Edit|Replace and replaced all C's with B's .... oops....

    I have corrected the formulas and the attachment.

  7. #7
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Look up function

    That's what we pilots call " finger trouble"

  8. #8
    Registered User
    Join Date
    08-20-2010
    Location
    scotland
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Look up function

    This is exactly what I'm looking for, will fill in the rest of the cells tonight and see how it goes.

    Thanks muchly.

  9. #9
    Registered User
    Join Date
    08-20-2010
    Location
    scotland
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Look up function

    Hi again,

    Just tried filling in the cells, however I cant get it the shifts by name page to bring over the shifts from Friday, and on a couple of thew people, i.e W cunningham I get a DIV ERROR for the time.

    Any thoughts, I have attached the spreadsheet.

    Cheers in advance

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Look up function

    For the Friday column, the first formula in B2 had the wrong column being referenced, should be coumn B. So change B2 formula to:

    =IF(ISNUMBER(MATCH($A2,Schedule!B$1:B$200,0)),LOOKUP(2,1/(Schedule!$A$1:INDEX(Schedule!$A$1:$A200,MATCH($A2,Schedule!B$1:B$200,0))<>""),Schedule!$A:$A),"")

    then copy B2 and B3 and paste to each cell corresponding to a name in column A..

    As for the second problem with the #Div/0! error, the formula relies on each Site occupying one cell in column A... House of Water should all be in one cell. Please correct that, as that is the best route... (there are other like that too).

  11. #11
    Registered User
    Join Date
    08-20-2010
    Location
    scotland
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Look up function

    Superb all working. Cheers for the help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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