Forum Discussion

null null's avatar
null null
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's avatar
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's avatar
    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's avatar
      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's avatar
        Phillip J. Seaman
        Copper Contributor
        Apr 19, 2018

        Damien... I was missing the $ in front of the row... thanks works great.

        Phill

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 18, 2018
      $L$3

Resources