How to ensure formulas references move when cell is moved without redoing formula

[sgcannon]

I have multiple formulas that reference a certain cell but now I have to move that cell to a new location. I may have to move it again in the future. I want to have all the references to that cell reference the new location when I move the cell. I know that one way I can do it is to name the cell. When I move the contents of that cell to its new location, I just have to update the location in the name definition. I know that will work but it seems like someone showed me another way to accomplish the same thing. Is the name definition method the best way to go?

Thanks,

Steve

 

[MarkRush]

I have multiple formulas that reference a certain cell but now I have to move that cell to a new location. I may have to move it again in the future. I want to have all the references to that cell reference the new location when I move the cell. I know that one way I can do it is to name the cell. When I move the contents of that cell to its new location, I just have to update the location in the name definition. I know that will work but it seems like someone showed me another way to accomplish the same thing. Is the name definition method the best way to go?

Thanks,


Steve

build the formula with the $ as an example in cell B15 you have a formula " =B$14" this will protect the column number but update the row. So if you copied to C15 the formula would show = C$14 If you enter the reference as $B$14 it retain the row and column

 

[sgcannon]

build the formula with the $ as an example in cell B15 you have a formula " =B$14" this will protect the column number but update the row. So if you copied to C15 the formula would show = C$14 If you enter the reference as $B$14 it retain the row and column

What a useful trick! Thank you.