Keeping part of Formula constant whilst the rest changes.

**Manuel Pedro** · 05-09-2012, 09:29 AM

Hi all.

I am busy with an excel spreadsheet that will track 200 employees's daily hours, Productivity, attendance, payslips and bonuses over a period of 12 months.

I have managed to resolve some of the bigger issues, but I need to know how to copy a formula where some parts must change whilst another part must stay exactly the same throughout.

EG:
=SUM(A5:D5)-B2
=SUM(A6:D6)-B2
=SUM(F5:I5)-B2
=SUM(F6:I6)-B2

As yon can see, I Need the range in the columns to change, but where they are subtracted from must stay constant.

Any help with this will be appreciated.

Thanks
Manny

**amotto11** · 05-09-2012, 09:43 AM

This would be =SUM(A5:D5)-$B$2 once you type your formula, click on anything you want to stay constant and press F4, this will put dollar signs around the column and the row, press it again and you can keep only the row locked down, again and it keeps only the column locked, and again turns it off. In your case you would click on B2 in your formula and press F4 once and just drag your formula down from there.