Forum Discussion

Copper Contributor

Feb 16, 2018

Constants in formulas

I still use @SUM() formatting for my Excel93 formulas. I have payroll worksheets that have a line for each week of the year. I enter the number of days worked (C1) multiplied by pay rate (D10) to g...

Formulas & Functions

Need Help

Damien_Rosario

Silver Contributor

Feb 16, 2018

Hi there

Adjust your formula slightly by using $ within the cell reference, in your case it would be $D$10.

This is called Absolute Cell Referencing and will ensure that the cell reference for your Pay Rate won't change.

Hope that helps.

Cheers
Damien

Phillip J. Seaman

Copper Contributor

Apr 18, 2018

Hi Damien,

I have the same issue and your solution did not work for me... let me elaborate a bit...

Cell L3 value = $0.29

I have a table that has rows of calculated fields that have the following formula in each =IF(D7=-10, ,D7*L3)

I have the same issue if I copy and paste this cells formula it changes the D reference but also the L reference. D SHOULD change but L should remain L3...

I tried putting a $ sign in front of the L (i.e. =IF(D7=-10, ,D7*$L3) - FAILED

So where do I put the "$"?

Thanks for your help.

Damien_Rosario
Silver Contributor
Apr 18, 2018
Hi Phillip

The $ locks in the column/row.

So $L locks in column L and $3 locks in row 3.

As Sergei says above, $L$3 will do the trick.

Cheers
Damien
- Phillip J. Seaman
  Copper Contributor
  Apr 19, 2018
  Damien... I was missing the $ in front of the row... thanks works great.
  Phill
  - Damien_Rosario
    Silver Contributor
    Apr 19, 2018
    No worries Phillip. It happens!
    
    All the best!
    
    Cheers
    Damien
SergeiBaklan
Diamond Contributor
Apr 18, 2018
$L$3