Linking Cells to Calculate - IF function?

[Sambrowne]

I have a spreadsheet with one sheet that has fixed cells for employee hours and another sheet where I enter leavers and joiners. This latter sheet is not fixed and is free text albeit with drop down occupation codes that are the same between the two sheets. What I want to do is enter a leaver or a joiner on one sheet and have it update the main hours sheet but because the second sheet is effectively free text I can't see how to do it. One cell can't be linked permanently to the other. My gut feeling is through the job codes. So when someone enters leaving hours for UNM (Unit Manager) it links to the other sheet through UNM and updates the hours accordingly. I think this is an IF function but I'm a bit stumped. Thanks!

 

[dreid1011]

Okay, that is a simple fix. In the LOOKUP formula, change the end "" to 0.

=XLOOKUP($D5&H$4,'Onboarders and Leavers'!$E$5:$E$25&'Onboarders and Leavers'!$F$5:$F$25,'Onboarders and Leavers'!$G$5:$G$25,0)

And with that, you could even take the IFERROR off the other formula if you want.

 

[Sambrowne]

I was about to ask exactly that. Wow mate, I think you've nailed it! I'd never have got there, not in a month of Sundays so can't thank you enough. I'm going tp put it all together tomorrow, lock it down where I can and let it loose.

Thanks again, I'd buy you a pint if I could!

 

[dreid1011]

You're welcome, and I am glad to help.

 

[Sambrowne]

Aargh, have discovered a slight glitch. If you see Ser 5 and 6 below both are CAR which would give a total of 60 hours but the formula only takes the first entry - John Smith - and doesn't seem to recognise Jane Doe. When they are pulled through to Ramp Scenarios I can see only 40. any ideas?

				Use drop down
	Ser	Date	Name	JC	Status	Hours	Notice	LWD	Start	Comments
	1	18-Oct-23​	Random	UNM	Onboarding	44.00​
	2	09-Oct-23​	Random	CHE	Onboarding	24.00​
	3		Random	HRL	Onboarding	40.00​			02-Nov-23​
	4		Random	RAL	Onboarding	30.00​			02-Nov-23​
	5		John Smith	CAR	Onboarding	40.00​
	6		Jane Doe	CAR	Onboarding	20.00​
	7
	8
	9
	10
	11
	12
	13
	14
	15
	16
	17
	18
	19
	20
	21

 

[dreid1011]

Okay, we can change the formula to fix that. But now I notice you have two entries on Ramp sheet for CAR, "CAR Days" and "CAR Nights". Both of these would pull in 60 from the 2nd sheet because they both have CAR in JC column (col D). Is this the desired result?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1
2			Ramp Scenarios
3			Careblox		Use drop down		0
4		Ser	Role	JC	R3	Ramp + flex	Contracted	Onboarding	Leaving	Recruit	Comments
5		1	DM	DEP	40	40	40	0	0	0
6		2	Adv Senior Carer	ADC	40	40	0	0	0	40
7		3	Unit Mgr	UNM	101.25	101.25	39.75	44	37.5	55
8		4	SCR Days	SCR	315	315	198.75	0	0	116.25	171.25
9		5	CAR Days	CAR	462	462	529	60	20	-107
10		6	SCR Nights	SCR	236.25	236.25	122	0	0	114.25
11		7	CAR Nights	CAR	231	231	308	60	20	-117
12		8	Maintenance	MAI	37.5	37.5	37.5	0	0	0
13		9	Gardener	MAI	25	25	0	0	0	25
14		10	Admin	ADM	40	40	37.5	0	0	2.5
15		18	Reception	REC	77	77	55.5	0	0	21.5
16		11	Head Chef	HCF	40	40	40	0	0	0
17		12	Sous Chef	SOU	40	40	0	0	0	40
18		13	Commis Chef	CHE	25	25	0	24	0	1
19		14	Kitchen Asst	KIT	80	80	80	0	0	0
20		15	Head Housekeeper	SHO	37.5	37.5	37.5	0	0	0
21		16	Housekeepers and Laundry Consolidated	HOU	120	120	151	0	0	-31
22		17	Housekeeper	HOU	62.5	62.5	121	0	0	-58.5	Careblox data includes laundry
23		18	Laundry Asst	LAU	58	58	30	0	0	28	See note above
24		19	Act Coord	HRL	38	38	0	40	0	-2
25		20	Act Asst	RAL	33	33	0	30	0	3
26		21	Staff Wellbeing		4	4	0	0	0	4
27		22	Driver	DRV	14	14	0	0	0	14
28						0
29		23	Totals		2036.5	2036.5	1676.5	258	77.5	179.5
Ramp

Cell Formulas
Range	Formula
H5:I27	H5	=SUMIFS('Onboarders and Leavers'!$G$5:$G$25,'Onboarders and Leavers'!$E$5:$E$25,$D5,'Onboarders and Leavers'!$F$5:$F$25,H$4)
J22:J27,J5:J20	J5	=F5+I5-(G5+H5)
K8	K8	=(J8+J7)
E5	E5	=IF(E4="","",IF(E4="R1",40,IF(E4="R2",40,IF(E4="R3",40,IF(E4="R4",40,IF(E4="Ramp",0,"Check Entry"))))))
E6	E6	=IF(E4="","",IF(E4="R1",40,IF(E4="R2",40,IF(E4="R3",40,IF(E4="R4",40,IF(E4="Ramp",0,"Check Entry"))))))
E7	E7	=IF(E4="","",IF(E4="R1",101.25,IF(E4="R2",101.25,IF(E4="R3",101.25,IF(E4="R4",101.25,IF(E4="Ramp",0,"Check Entry"))))))
E8	E8	=IF(E4="","",IF(E4="R1",157.5,IF(E4="R2",236.25,IF(E4="R3",315,IF(E4="R4",315,IF(E4="Ramp",0,"Check Entry"))))))
E9	E9	=IF(E4="","",IF(E4="R1",308,IF(E4="R2",385,IF(E4="R3",462,IF(E4="R4",539,IF(E4="Ramp",0,"Check Entry"))))))
E10	E10	=IF(E4="","",IF(E4="R1",157.5,IF(E4="R2",236.25,IF(E4="R3",236.25,IF(E4="R4",315,IF(E4="Ramp",0,"Check Entry"))))))
E11	E11	=IF(E4="","",IF(E4="R1",154,IF(E4="R2",154,IF(E4="R3",231,IF(E4="R4",231,IF(E4="Ramp",0,"Check Entry"))))))
E12	E12	=IF(E4="","",IF(E4="R1",37.5,IF(E4="R2",37.5,IF(E4="R3",37.5,IF(E4="R4",37.5,IF(E4="Ramp",0,"Check Entry"))))))
E13	E13	=IF(E4="","",IF(E4="R1",0,IF(E4="R2",25,IF(E4="R3",25,IF(E4="R4",25,IF(E4="Ramp",0,"Check Entry"))))))
E14	E14	=IF(E4="","",IF(E4="R1",40,IF(E4="R2",40,IF(E4="R3",40,IF(E4="R4",40,IF(E4="Ramp",0,"Check Entry"))))))
E15	E15	=IF(E4="","",IF(E4="R1",77,IF(E4="R2",77,IF(E4="R3",77,IF(E4="R4",77,IF(E4="Ramp",0,"Check Entry"))))))
E16	E16	=IF(E4="","",IF(E4="R1",40,IF(E4="R2",40,IF(E4="R3",40,IF(E4="R4",40,IF(E4="Ramp",0,"Check Entry"))))))
E17	E17	=IF(E4="","",IF(E4="R1",40,IF(E4="R2",40,IF(E4="R3",40,IF(E4="R4",40,IF(E4="Ramp",0,"Check Entry"))))))
E18	E18	=IF(E4="","",IF(E4="R1",0,IF(E4="R2",25,IF(E4="R3",25,IF(E4="R4",25,IF(E4="Ramp",0,"Check Entry"))))))
E19	E19	=IF(E4="","",IF(E4="R1",80,IF(E4="R2",80,IF(E4="R3",80,IF(E4="R4",80,IF(E4="Ramp",0,"Check Entry"))))))
E21	E21	=IF(E4="","",IF(E4="R1",78.5,IF(E4="R2",88.5,IF(E4="R3",120,IF(E4="R4",134,IF(E4="Ramp",0,"Check Entry"))))))
E22	E22	=IF(E4="","",IF(E4="R1",38.5,IF(E4="R2",48.5,IF(E4="R3",62.5,IF(E4="R4",76.5,IF(E4="Ramp",0,"Check Entry"))))))
E23	E23	=IF(E4="","",IF(E4="R1",40,IF(E4="R2",40,IF(E4="R3",58,IF(E4="R4",58,IF(E4="Ramp",0,"Check Entry"))))))
E24	E24	=IF(E4="","",IF(E4="R1",38,IF(E4="R2",38,IF(E4="R3",38,IF(E4="R4",38,IF(E4="Ramp",0,"Check Entry"))))))
E25	E25	=IF(E4="","",IF(E4="R1",17,IF(E4="R2",17,IF(E4="R3",33,IF(E4="R4",33,IF(E4="Ramp",0,"Check Entry"))))))
E26	E26	=IF(E4="","",IF(E4="R1",4,IF(E4="R2",4,IF(E4="R3",4,IF(E4="R4",4,IF(E4="Ramp",0,"Check Entry"))))))
E27	E27	=IF(E4="","",IF(E4="R1",14,IF(E4="R2",14,IF(E4="R3",14,IF(E4="R4",14,IF(E4="Ramp",0,"Check Entry"))))))
F5:F29	F5	=(E5*$G$3)+E5
E29,G29:J29	E29	=SUM(E5:E21)+SUM(E24:E27)

 

[Sambrowne]

Aye, spotted that this morning when I was trying to work out what was going on. I've changed both data validation sets so that they have CAR and CAR(N) and SCR and SCR(N), that should do the trick?

 

[dreid1011]

Aye, spotted that this morning when I was trying to work out what was going on. I've changed both data validation sets so that they have CAR and CAR(N) and SCR and SCR(N), that should do the trick?

Yes, just do that or something similar to the other duplicates if you need them to pull separately. (MAI & HOU)

 

[Sambrowne]

The others are ok but there's still a chance that I will have multiple entries on the Onboarders and Leavers sheet for CAR (and CAR(N )as there are several of those posts in the business and quite often I'll take on two or three at a time. Same goes for SCR and SCR(N).

 

[dreid1011]

The others are ok but there's still a chance that I will have multiple entries on the Onboarders and Leavers sheet for CAR (and CAR(N )as there are several of those posts in the business and quite often I'll take on two or three at a time. Same goes for SCR and SCR(N).

Do those changes solve the problem then?

 

[Sambrowne]

No, it still only pulls through the first entry onto Ramp. Ramp should say 50 for CAR not 20?