My formula is unnecessarily copied to the adjacent cell when entering data into another cell

[MrsGrayMarlin]

I am a payroll professional. I use an Excel spreadsheet to track regular/PTO/holiday, etc., hours to compare to our payroll processing software totals. I am using Microsoft® Excel® for Microsoft 365 MSO (Version 2408 Build 16.0.17928.20114) 64-bit

For each employee, their line contains their regular bi-weekly hours (usually 80) in column L. Columns M, and N are titled "Hourly" and "Salary," respectively, depending on the employee's classification. Columns M or N contain the formula "=L69-O69-R69-S69-T69-U69-V69-W69-AA69-AF69-AG69” where “L” is their regular hours and all other columns represent Unpaid Time off (O), PTO Week 1 (R), PTO Week 2 (S), Holiday Week 1 (T), Holiday Week 2 (U), Floating Holiday Week 1 (V), Floating Holiday Week 2 (W), Jury Duty (AA), Bereavement Week 1 (AF), Bereavement Week 2 (AG).

I have a master template sheet at the beginning of the workbook. For each two-week pay period, I copy the template using right-click, move, or copy to a new sheet and name it with the pay period's dates. This issue occurs whether I follow that process or if I copy all data and paste it to a new worksheet.

When entering holiday or PTO hours into columns T or U, the formula in column M or N (again, depending on the employee classification) gets copied to the cell, which does not contain the data in column M or N. For example, if employee A occupies row 45, and is an hourly employee, their formula will reside in cell M45. Once I enter 8 hours to column T to indicate 8 hours of holiday pay, the formula will copy to cell N45. That means I have data in M45 and N45, which throws off my column totals.

Does anyone know why this might be happening? Please let me know if you need screenshots or any other information to assist me.

Thank you!

 

[Sergius]

It would be nice if you could provide a small example of your table. As it is and how you would like the result to look like.

 

[MrsGrayMarlin]

Unfortunately, my company computer will not allow me to download the XL2BB Add-In so this is the best I can do. The names in the table are fictitious. The table starts in cell A1. In this example, when entering data in columns P or Q (PTO Week 1/PTO Week 2), the formula in Column K (Hourly) is copied to Column L. This is the undesired action. When entering data in any cell, it should not copy to another cell when there is not a formula.
Thanks for your help!

Employee

Staff Type/Dept

UKG EE ID

Number of Work Days

Number of Hours / Day

Daily Points for PTO (Quota)

Work day schedule

Hourly/Salary

Branch

Regular Hours (For Calc Only)

Hourly

Salary

Unpaid

1099

OT

PTO - Week 1 (hrs)

PTO - Week 2 (hrs)

Total Hours - UKG

Adam Cherry

LPN

419

5

8

8

Hourly

NH

80.00​

80.00​

80.00​

Ahmed Blackburn

OT

201

5

8

6

Salary

RH

80.00​

80.00​

80.00​

Amari Gonzales

RN

308

4

10

8.75

Mon-Thu (40 hr)

Salary

RH

80.00​

80.00​

80.00​

Ayaan Miles

RN

227

4

8

7

Tue-Fri (32 hr)

Salary

NH

64.00​

64.00​

64.00​

Caitlyn Gibbs

PT

137

5

8

6

Salary

NH

80.00​

80.00​

80.00​

Destinee Sweeney

RN - Admissions

355

4

8

7

Mon,Tue,Wed,Fri (32 hr)

Salary

RH

64.00​

64.00​

64.00​

Eleanor Lawrence

PT

298

5

8

6

Salary

NH

80.00​

80.00​

80.00​

Giselle Heath

PT

45

5

8

5

Salary

RH

80.00​

80.00​

80.00​

Howard Mann

PT

206

5

8

6

Tue-Fri (40 hr)

Salary

NH

80.00​

80.00​

80.00​

Jair Boone

LPN

374

5

8

8

Hourly

RH

80.00​

80.00​

80.00​

Jaylah Dodson

RN

354

5

8

7

Salary

RH

80.00​

80.00​

80.00​

Jazmyn Zamora

PT

274

5

8

6

Salary

NH

80.00​

80.00​

80.00​

Joslyn Blanchard

RN

396

5

8

7

Salary

RH

80.00​

80.00​

80.00​

Karlee Burton

RN

205

5

8

6.4

Mon-Thu (40 hr)

Salary

NH

80.00​

80.00​

80.00​

Kathy Rhodes

RN

243

5

8

7

Salary

NH

80.00​

80.00​

80.00​

Kevin Vargas

RN

373

5

8

5

Salary

NH

80.00​

80.00​

80.00​

Lillian Randall

OT

406

5

8

6

Salary

RH

80.00​

80.00​

80.00​

Lydia Hamilton

OT

208

4

10

7.5

Mon-Thu (40hr)

Salary

NH

80.00​

80.00​

80.00​

Sergio Yang

PT

165

3

8

6

Mon,Tue,Thu (24 hr)

Hourly

RH

48.00​

48.00​

48.00​

Viviana Kane

OT

299

5

8

6

Salary

NH

80.00​

80.00​

80.00​

 

[RoryA]

Without the workbook, it's a bit of guesswork. I'd suspect it's a table column autofilling, or flash fill. Why don't you just put the formula in both columns on each row, but have it check the Hourly/Salary column before calculating. So it would be something like:
=IF(H2="Hourly", your formula, 0)
and in the next column:
=IF(H2="Salary", your formula,0)

 

[MrsGrayMarlin]

Without the workbook, it's a bit of guesswork. I'd suspect it's a table column autofilling, or flash fill. Why don't you just put the formula in both columns on each row, but have it check the Hourly/Salary column before calculating. So it would be something like:
=IF(H2="Hourly", your formula, 0)
and in the next column:
=IF(H2="Salary", your formula,0)

Hello,
I was able to transfer the file to my personal computer, download the software, and upload it from there. Please see below and advise if your solution suggestion remains the same. Thank you in advance for your time!

Payroll Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q															AF
1	Employee	Staff Type/Dept	UKG EE ID	Number of Work Days	Number of Hours / Day	Daily Points for PTO (Quota)	Work day schedule	Hourly/Salary	Branch	Regular Hours (For Calc Only)	Hourly	Salary	Unpaid	1099	OT	PTO - Week 1 (hrs)	PTO - Week 2 (hrs)															Total Hours - UKG
2	Adam Cherry	LPN	419	5	8	8		Hourly	NH	80.00	80.00																					80.00
3	Ahmed Blackburn	OT	201	5	8	6		Salary	RH	80.00		80.00																				80.00
4	Amari Gonzales	RN	308	4	10	8.75	Mon-Thu (40 hr)	Salary	RH	80.00		80.00																				80.00
5	Ayaan Miles	RN	227	4	8	7	Tue-Fri (32 hr)	Salary	NH	64.00		64.00																				64.00
6	Caitlyn Gibbs	PT	137	5	8	6		Salary	NH	80.00		80.00																				80.00
7	Destinee Sweeney	RN - Admissions	355	4	8	7	Mon,Tue,Wed,Fri (32 hr)	Salary	RH	64.00		64.00																				64.00
8	Eleanor Lawrence	PT	298	5	8	6		Salary	NH	80.00		80.00																				80.00
9	Giselle Heath	PT	45	5	8	5		Salary	RH	80.00		80.00																				80.00
10	Howard Mann	PT	206	5	8	6	Tue-Fri (40 hr)	Salary	NH	80.00		80.00																				80.00
11	Jair Boone	LPN	374	5	8	8		Hourly	RH	80.00	80.00																					80.00
12	Jaylah Dodson	RN	354	5	8	7		Salary	RH	80.00		80.00																				80.00
13	Jazmyn Zamora	PT	274	5	8	6		Salary	NH	80.00		80.00																				80.00
14	Joslyn Blanchard	RN	396	5	8	7		Salary	RH	80.00		80.00																				80.00
15	Karlee Burton	RN	205	5	8	6.4	Mon-Thu (40 hr)	Salary	NH	80.00		80.00																				80.00
16	Kathy Rhodes	RN	243	5	8	7		Salary	NH	80.00		80.00																				80.00
17	Kevin Vargas	RN	373	5	8	5		Salary	NH	80.00		80.00																				80.00
18	Lillian Randall	OT	406	5	8	6		Salary	RH	80.00		80.00																				80.00
19	Lydia Hamilton	OT	208	4	10	7.5	Mon-Thu (40hr)	Salary	NH	80.00		80.00																				80.00
20	Sergio Yang	PT	165	3	8	6	Mon,Tue,Thu (24 hr)	Hourly	RH	48.00	48.00																					48.00
21	Viviana Kane	OT	299	5	8	6		Salary	NH	80.00		80.00																				80.00
Sheet1

Cell Formulas
Range	Formula
K2,K20,K11	K2	=J2-M2-P2-Q2-R2-S2-T2-U2-Y2-AD2-AE2
L3:L10,L21,L12:L19	L3	=J3-M3-P3-Q3-R3-S3-T3-U3-Y3-AD3-AE3
H2:H21	H2	=IF(K2>=1,"Hourly","Salary")
AF2,AF11,AF20	AF2	=SUM(K2:AE2)
AF3:AF10,AF12:AF19,AF21	AF3	=SUM(L3:AE3)

 

[RoryA]

Yes it does.

I hope that is not real data?

 

[MrsGrayMarlin]

Yes it does.

I hope that is not real data?

The names are fictitious