Forum Discussion

George1970's avatar
George1970
Copper Contributor
Jan 16, 2022

Formula in excel tabel

Hi!

I need help with this formula in a excel tabel, I have two sheets named "Temerpatur" and " Klara jobb". Problem is when I cut one line from "temperatur" sheet and paste it in "klara jobb" sheet gets my formula changed automaticaly in "temperatur" sheet. How can I lock this formula in a excel tabel ?

Before cutting:  =IF(H6="";"";DAYS360($I$1;TEMPERATUR!$H6))

After pasting: =IF('KLARA JOBB'!H6="";"";DAYS360($I$1;TEMPERATUR!$H6))

Best regards:

George1970

Formulas and Functions

3 Replies

  • bosinander's avatar
    bosinander
    Iron Contributor
    Feb 05, 2022
    Since you cut, you will still get the exactly same result as where it was, ie using data from temperatur.
    If you instead copy, the formula will adapt to the klara sheet (done).
    Then you have to go back to temperatur and clear the original line, or remove it (Ctrl+- ie, press ctrl and type minus)
    • George1970's avatar
      George1970
      Copper Contributor
      Feb 20, 2022
      Thank you, but is there any other solution to lock a formula in a table which does not change between sheets.
      Regards // George
      • bosinander's avatar
        bosinander
        Iron Contributor
        Feb 20, 2022

        Hi George1970 I may have misinterpreted what you wanted to do so here is another try.

        Instead of having the first H6 without the sheet name, 

        =IF(H6="";"";DAYS360(Temperatur!$I$1;Temperatur!$H6))

        use

        =IF(Temperatur!H6="";"";DAYS360(Temperatur!$I$1;Temperatur!$H6))

        and that formula can be moved to KLARA and still refer to the value in Temperatur!H6.

        Just like your other two cell references.

         

        Yet another possibility is that you could use INDIRECT("H6";1) insteda of H6. 

        It depends on what the moved line contains - H6 or the formula that refers to H6.

        Indirect is a volatile function and thus not as fast as others since it is recalculated whether it is needed or not. Probably not a problem for smaller models.

Resources