lock cell in formula even when column is inserted?

[Bret1]

HI. Is there a way to lock in a cell in a formula even if a column is inserted? I know "$" locks in cells, but it doesn't work if I insert a new column. If I put "=SUM($E$10)" in A10, then insert column at D, it still changes the formula to "=SUM($F$10)" in cell A10. I want it to stay "=SUM($E$10)". Thanks

 

[Joe4]

One trick you can use is the INDIRECT function (because then the range is actually a string, and not a range, so Excel won't change it), i.e.

=SUM(INDIRECT("$E$10"))

Note: I hope this is just a simplified example, because I am not sure what the point would be of applying the SUM function to a single cell. SUM is used to add up multiple values.

 

[Bret1]

One trick you can use is the INDIRECT function (because then the range is actually a string, and not a range, so Excel won't change it), i.e.

=SUM(INDIRECT("$E$10"))

Note: I hope this is just a simplified example, because I am not sure what the point would be of applying the SUM function to a single cell. SUM is used to add up multiple values.

Thanks!

 

[Joe4]

You are welcome.
Glad I was able to help!