Formula in excel tabel

%3CLINGO-SUB%20id%3D%22lingo-sub-3063112%22%20slang%3D%22en-US%22%3EFormula%20in%20excel%20tabel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3063112%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3EI%20need%20help%20with%20this%20formula%20in%20a%20excel%20tabel%2C%20I%20have%20two%20sheets%20named%20%22Temerpatur%22%20and%20%22%20Klara%20jobb%22.%20Problem%20is%20when%20I%20cut%20one%20line%20from%20%22temperatur%22%20sheet%20and%20paste%20it%20in%20%22klara%20jobb%22%20sheet%20gets%20my%20formula%20changed%20automaticaly%20in%20%22temperatur%22%20sheet.%20How%20can%20I%20lock%20this%20formula%20in%20a%20excel%20tabel%20%3F%3C%2FP%3E%3CP%3EBefore%20cutting%3A%26nbsp%3B%20%3DIF(H6%3D%22%22%3B%22%22%3B%3CSTRONG%3EDAYS360%3C%2FSTRONG%3E(%24I%241%3BTEMPERATUR!%24H6))%3C%2FP%3E%3CP%3EAfter%20pasting%3A%20%3DIF('KLARA%20JOBB'!H6%3D%22%22%3B%22%22%3B%3CSTRONG%3EDAYS360%3C%2FSTRONG%3E(%24I%241%3BTEMPERATUR!%24H6))%3C%2FP%3E%3CP%3EBest%20regards%3A%3C%2FP%3E%3CP%3EGeorge1970%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3063112%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3121875%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20in%20excel%20tabel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3121875%22%20slang%3D%22en-US%22%3ESince%20you%20cut%2C%20you%20will%20still%20get%20the%20exactly%20same%20result%20as%20where%20it%20was%2C%20ie%20using%20data%20from%20temperatur.%3CBR%20%2F%3EIf%20you%20instead%20copy%2C%20the%20formula%20will%20adapt%20to%20the%20klara%20sheet%20(done).%3CBR%20%2F%3EThen%20you%20have%20to%20go%20back%20to%20temperatur%20and%20clear%20the%20original%20line%2C%20or%20remove%20it%20(Ctrl%2B-%20ie%2C%20press%20ctrl%20and%20type%20minus)%3C%2FLINGO-BODY%3E
New Contributor

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

3 Replies
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)
Thank you, but is there any other solution to lock a formula in a table which does not change between sheets.
Regards // George

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.