Roster lookup table - v/h/x lookup?

[Kariba]

Hi, after some advice please.

I have created a roster - small snapshot below. I also have daily worksheets and need to copy across the names and shifts on a daily basis. The obvious way is just to copy/paste the name column and then the relevant date column. The full roster is quite large and the destination file is in a slightly different format so needs some tidying up/formatting/sorting into time order etc even with copy/paste so seeing if there are easier ways to do it automatically. VLookup won't work as I'd need the names already in the destination file to refer back to the source file. I don't know much about xlookup as I'm new to it - would that work?

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1				Lieu Days	Hol Entitlement	Total	Booked	Remaining	Fri	Sat	Sun	Mon	Tue	Wed
2									27-Oct-23	28-Oct-23	29-Oct-23	30-Oct-23	31-Oct-23	01-Nov-23
3	1	Name1	DRIVER - DAYS						5-13:00	5-13:00	5-13:00			12-20:00
4	1	Name1	DRIVER - DAYS	34	88	122	0	122
5	2	Name2	DRIVER - DAYS						5-13:00	5-13:00	5-13:00			12-20:00
6	2	Name2	DRIVER - DAYS	87	56	143	0	143
7	3	Name3	DRIVER - DAYS						5-13:00	5-13:00	5-13:00			12-20:00
8	3	Name3	DRIVER - DAYS	34	97	131	0	131
9	4	Name4	DRIVER - DAYS						5-13:00	5-13:00	5-13:00			12-20:00
10	4	Name4	DRIVER - DAYS	24	0	24	0	24
11	5	Name5	DRIVER - DAYS						5-13:00	5-13:00	5-13:00			12-20:00
12	5	Name5	DRIVER - DAYS	62	88	150	8	142						H8
13	6	Name6	DRIVER - DAYS						5-13:00	5-13:00	5-13:00			12-20:00
14	6	Name6	DRIVER - DAYS	88	160	248	0	248
Sheet1

Cell Formulas
Range	Formula
I1:N1	I1	=I2
J2:N2	J2	=I2+1
F4,F14,F12,F10,F8,F6	F4	=D4+E4
G4,G14,G12,G10,G8,G6	G4	=COUNTIFS($I4:$FM4, "H4")*4 +COUNTIFS($I4:$FM4, "H6")*6+COUNTIFS($I4:$FM4, "H8")*8 + COUNTIFS($I4:$FM4, "H10")*10 + COUNTIFS($I4:$FM4, "H12")*12+COUNTIFS($I4:$FM4, "LD8")*8 + COUNTIFS($I4:$FM4, "LD10")*10 + COUNTIFS($I4:$FM4, "LD12")*12
H4,H14,H12,H10,H8,H6	H4	=F4-G4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:N2	Dates Occurring	today	text	NO
I3:N14	Cell Value	contains "S"	text	NO
H3:H14	Cell Value	<0	text	NO
A1:C1,I1:N1	Cell Value	="FRI"	text	NO

 

[MountainFog]

can you provide an example of where you want the names to pull from? And then what you would want the result to be?

Chances are that if VLOOKUP won't work, XLOOKUP won't work either. XLOOKUP is just an updated version of vlookup that adds in hlookup functionality. (XLookup can be used either vertically or horizontally.

 

[Kariba]

OK thanks. This is the source data. Wanted to 'export' the relevant column from here to the daily sheets. I wasn't going to copy/paste from here but data wouldn't be live and any changes made here wouldn't show on daily sheets. Trying to make it as easy and foolproof for the regular users as possible.