Linking Cells to Calculate - IF function?

S

Sambrowne

New Member
Joined
Sep 13, 2016
Messages
46
Office Version
  1. 365
Platform
  1. MacOS
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!
 

Attachments

  • Sheet 1 - Main.png
    Sheet 1 - Main.png
    17.1 KB · Views: 9
  • Sheet 2 - Free, fixed codes.png
    Sheet 2 - Free, fixed codes.png
    18.6 KB · Views: 10

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
Tying my small brain in knots. I'm trying to say that 'IF a cell in the range C5:J10 in sheet 2 contains the word UNM (or another reference, whatever makes it work) then subtract or add number in relevant corresponding cell to sheet 1 fixed cell.

So using the UNM in sheet1 as the reference, I'm trying to say that if UNM appears anywhere in range C5:J10 on sheet, it automatically links to D7 on Sheet 1. I then need to say subtract or add number of onboarding/leaving hours from sheet 2 to sheet 1 UNM row. I can do it if sheet two is fixed but not if the data is entered as it occurs. In the example above it's G5 for UNM leaving hours but it could be G6, 7 and so on,.

Not even sure I'm explaining it very well! MATCH function maybe?
 
Last edited:
Upvote 0
Sambrowne said:
Tying my small brain in knots. I'm trying to say that 'IF a cell in the range C5:J10 in sheet 2 contains the word UNM (or another reference, whatever makes it work) then subtract or add number in relevant corresponding cell to sheet 1 fixed cell.

So using the UNM in sheet1 as the reference, I'm trying to say that if UNM appears anywhere in range C5:J10 on sheet, it automatically links to D7 on Sheet 1. I then need to say subtract or add number of onboarding/leaving hours from sheet 2 to sheet 1 UNM row. I can do it if sheet two is fixed but not if the data is entered as it occurs. In the example above it's G5 for UNM leaving hours but it could be G6, 7 and so on,.

Not even sure I'm explaining it very well! MATCH function maybe?
Click to expand...
Which column on sheet 1 should hours from sheet 2 be entered in?
 
Upvote 0
If they're leaving H1 and if they're onboarding, G1. Reading a bit more last night and the LOOKUP feature might work?
 
Upvote 0
Sambrowne said:
If they're leaving H1 and if they're onboarding, G1. Reading a bit more last night and the LOOKUP feature might work?
Click to expand...
Yes, a LOOKUP formula could work here. Maybe this:

Book1
BCDEFGHIJK
2Ramp Scenarios
3
4SerRoleJCR3Ramp + flexContractedOnboardingLeavingRecruitComments
51DMDEP404040  0
62Adv Senior CarerADC40400  40
73Unit MgrUNM101.25101.2539.7544-37.517.5
84SCR DaysSCR315315198.75  116.25
Main
Cell Formulas
RangeFormula
H5:I8H5=XLOOKUP($D5&H$4,'Free, fixed codes'!$E$5:$E$10&'Free, fixed codes'!$F$5:$F$10,'Free, fixed codes'!$G$5:$G$10,"")


Book1
BCDEFGHIJK
4SerDateNameJCStatusHoursNoticeLWDStartComments
519/22/2023UNMLeaving-37.5010/22/2023
6210/18/2023UNMOnboarding44.00tbc
7310/19/2023CARLeaving-20.0010/20/2023
8410/9/2023CHEOnboarding24.00
95HRLOnboarding40.0011/2/2023
106RALOnboarding30.0011/2/2023
Free, fixed codes
 
Upvote 1
Thanks very much, I'm still struggling with this a bit. I'm not sure where to insert the formula you created and what else I need to do to it to make it work? I'll try and upload the doc tonight and hopefully that will make it easier, can't do it from my work laptop unfortunately. Thanks again though, appreciated.
 
Upvote 0
Sambrowne said:
Thanks very much, I'm still struggling with this a bit. I'm not sure where to insert the formula you created and what else I need to do to it to make it work? I'll try and upload the doc tonight and hopefully that will make it easier, can't do it from my work laptop unfortunately. Thanks again though, appreciated.
Click to expand...
According to your sample data, the formula goes in columns H and I to match the Onboarding / Leaving amounts with the column headers, though when I asked, you said columns G and H.
 
Upvote 0
Sorry, I've attached a mini sheet that might make it a bit clearer?

Book1.xlsx
D
12
Onboarders and Leavers


Book1.xlsx
O
13
Ramps


Hope that works, first time I've used it.
 
Upvote 0
Sambrowne said:
Sorry, I've attached a mini sheet that might make it a bit clearer?

Book1.xlsx
D
12
Onboarders and Leavers


Book1.xlsx
O
13
Ramps


Hope that works, first time I've used it.
Click to expand...
Not quite. Your selection doesn't look like it worked. You have to select the whole range you want to copy for the mini sheet.
 
Upvote 0
Balls, try again:

Book1.xlsx
ABCDEFGHIJK
2Ramp Scenarios
3CarebloxUse drop down0%
4SerRoleJCR3Ramp + flexContractedOnboardingLeavingRecruitComments
51DMDEP4040400.00
62Adv Senior CarerADC4040040.00
73Unit MgrUNM101.25101.2539.754417.50
84SCR DaysSCR315315198.757739.2556.75
95CAR DaysCAR4624625297710.00
106SCR NightsSCR236.25236.251223381.25
117CAR NightsCAR2312313083344.00
128MaintenanceMAI37.537.537.50.00
139GardenerMAI2525025.00
1410AdminADM404037.51618.50
1518ReceptionREC777755.521.50
1611Head ChefHCF4040400.00
1712Sous ChefSOU4040040.00
1813Commis ChefCHE25250241.00
1914Kitchen AsstKIT8080800.00
2015Head HousekeeperSHO37.537.537.50.00
2116Housekeepers and Laundry ConsolidatedHOU12012015131.00
2217HousekeeperHOU62.562.512158.50Careblox data includes laundry
2318Laundry AsstLAU58583028.00See note above
2419Act CoordHRL38380402.00
2520Act AsstRAL33330303.00
2621Staff Wellbeing4404.00
2722DriverDRV1414014.00
280
2923Totals2036.52036.51676.5248126238
30
Ramps
Cell Formulas
RangeFormula
K8K8=(J8+J7)
E5E5=IF(E4="","",IF(E4="R1",40,IF(E4="R2",40,IF(E4="R3",40,IF(E4="R4",40,IF(E4="Ramp",0,"Check Entry"))))))
E6E6=IF(E4="","",IF(E4="R1",40,IF(E4="R2",40,IF(E4="R3",40,IF(E4="R4",40,IF(E4="Ramp",0,"Check Entry"))))))
E7E7=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"))))))
E8E8=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"))))))
E9E9=IF(E4="","",IF(E4="R1",308,IF(E4="R2",385,IF(E4="R3",462,IF(E4="R4",539,IF(E4="Ramp",0,"Check Entry"))))))
E10E10=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"))))))
E11E11=IF(E4="","",IF(E4="R1",154,IF(E4="R2",154,IF(E4="R3",231,IF(E4="R4",231,IF(E4="Ramp",0,"Check Entry"))))))
E12E12=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"))))))
E13E13=IF(E4="","",IF(E4="R1",0,IF(E4="R2",25,IF(E4="R3",25,IF(E4="R4",25,IF(E4="Ramp",0,"Check Entry"))))))
E14E14=IF(E4="","",IF(E4="R1",40,IF(E4="R2",40,IF(E4="R3",40,IF(E4="R4",40,IF(E4="Ramp",0,"Check Entry"))))))
E15E15=IF(E4="","",IF(E4="R1",77,IF(E4="R2",77,IF(E4="R3",77,IF(E4="R4",77,IF(E4="Ramp",0,"Check Entry"))))))
E16E16=IF(E4="","",IF(E4="R1",40,IF(E4="R2",40,IF(E4="R3",40,IF(E4="R4",40,IF(E4="Ramp",0,"Check Entry"))))))
E17E17=IF(E4="","",IF(E4="R1",40,IF(E4="R2",40,IF(E4="R3",40,IF(E4="R4",40,IF(E4="Ramp",0,"Check Entry"))))))
E18E18=IF(E4="","",IF(E4="R1",0,IF(E4="R2",25,IF(E4="R3",25,IF(E4="R4",25,IF(E4="Ramp",0,"Check Entry"))))))
E19E19=IF(E4="","",IF(E4="R1",80,IF(E4="R2",80,IF(E4="R3",80,IF(E4="R4",80,IF(E4="Ramp",0,"Check Entry"))))))
J5:J20,J22:J27J5=F5+I5-(G5+H5)
E21E21=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"))))))
E22E22=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"))))))
E23E23=IF(E4="","",IF(E4="R1",40,IF(E4="R2",40,IF(E4="R3",58,IF(E4="R4",58,IF(E4="Ramp",0,"Check Entry"))))))
E24E24=IF(E4="","",IF(E4="R1",38,IF(E4="R2",38,IF(E4="R3",38,IF(E4="R4",38,IF(E4="Ramp",0,"Check Entry"))))))
E25E25=IF(E4="","",IF(E4="R1",17,IF(E4="R2",17,IF(E4="R3",33,IF(E4="R4",33,IF(E4="Ramp",0,"Check Entry"))))))
E26E26=IF(E4="","",IF(E4="R1",4,IF(E4="R2",4,IF(E4="R3",4,IF(E4="R4",4,IF(E4="Ramp",0,"Check Entry"))))))
E27E27=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:F29F5=(E5*$G$3)+E5
E29,G29:J29E29=SUM(E5:E21)+SUM(E24:E27)
Cells with Data Validation
CellAllowCriteria
E4ListRamp, R1, R2, R3, R4


Book1.xlsx
ABCDEFGHIJK
1
2
3Use drop down
4SerDateNameJCStatusHoursNoticeLWDStartComments
5122-Sep-23Lady AliceUNMLeaving(37.50)########
6218-Oct-23Lord BulbousUNMOnboarding44.00tbc
7319-Oct-23Sir ChavCARLeaving(20.00)########
849-Oct-23Major ****CHEOnboarding24.00
95Mr EggHRLOnboarding40.002-Nov-23
106Mrs FlubRALOnboarding30.002-Nov-23
117
128
139
1410
1511
1612
1713
1814
1915
2016
2117
2218
2319
2420
2521
Onboarders and Leavers
Cells with Data Validation
CellAllowCriteria
E5:E25ListADM, ADC, CAR, CHE, DEP, DRV, HOU, HCF, HRL, KIT, LAU, MAI, MAN, REC, RAL, SCR, SHO, SOU, UNM
F5:F25ListOnboarding, Leaving
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Excel Sharepoint Linked Workbook - show blank for cells returning "0"s?
Replies
5
Views
69
Fluff
Fluff
H
  • Solved
Insert blank rows conditionally
Replies
3
Views
270
kevin9999
K
K
  • Question
How to Connect Employees' Individual Excel Workbooks to a Main Workbook for Automatic Data Updates?
Replies
4
Views
616
OaklandJim
O
spacecaptainsuperguy
  • Question
Advice for linked files on network drive
Replies
0
Views
335
spacecaptainsuperguy
spacecaptainsuperguy
N
  • Solved
Use different sheet names as part of a formula (indirect function?)
Replies
3
Views
320
awoohaw
awoohaw

Forum statistics

Threads
1,215,102
Messages
6,123,097
Members
449,096
Latest member
provoking

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top