Forum Discussion

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 ...

Formulas and Functions

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
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
  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.