# Look up function

1. ## 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.

2. ## 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. ## 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

4. ## 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

5. ## 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),"")

6. ## 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. ## Re: Look up function

That's what we pilots call " finger trouble"

8. ## 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. ## 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.

10. ## 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. ## Re: Look up function

Superb all working. Cheers for the help

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

#### 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