Transfering data automatically referencing from another cell

[Marvo]

Hi All, I need a bit of help starting something off. I work in an industry where there are numerous booking on and off times. Each job is given a number so for an example Turn 1 books on at 02:26 and books off at 10:27. I compile rosters and have to insert all these times individually into links and I thought if I could just connect the turn number with the time from a reference sheet, so put it "1" and 02:26 - 10:27 would automatically be entered in the adjacent cells it would save me loads of work. It would also help as every six months these turn times change, sometimes just by the odd minute but if I had a master copy I would only have to change that twice a year. Any suggestions?

[ARGK]

Hi
If I understand you correctly you can do this with VLOOKUP and, as you say, a reference table.
So if you have a reference table sitting in another workbook then use these formulas in your record sheet and change the workbook name and sheet name to match yours. This is assuming your reference data in in columns A to C and start in row 1:
=VLOOKUP(A2,'[Workbook Name.xlsx]SheetName'!$A$1:$C$20,2,FALSE) this will pick up the start time
=VLOOKUP(A2,'[Workbook Name.xlsx]SheetName'!$A$1:$C$20,3,FALSE) this will pick up the finish time
Hope this helps.
Good luck.
Tony

[Marvo]

Thanks Tony, I'm sure that will work just fine.

[Marvo]

No sorry, can't do it. Must be doing something wrong.

I made a workbook called "Turns" and on the first sheet named "Monday" I have three columns. A is the turn number, B is the starting time, C is the finishing time.
Opened up a new workbook to try it out and entered "1" in column A then your formula in Column B to see if it would give me the start time of Turn 1 but alas, it didn't work. Flummoxed.

[ARGK]

Hi
Can you post your sample workbook so I can take a look.
Tony

[Marvo]

Certainly. I've taken a snippet. On the first sheet there are just two jobs shown (both in green). What I'd like to do is be able to type in the job number and the hours come up automatically from the reference on the second sheet which shows jobs on Mondays only.

I could change the design of the link to show the job number before the start and finishing time if you think that would help?

Thanks so much for taking the time to look at this.

[Marvo]

It's okay, I've worked how to do it. Surprised myself.