Google Spreadsheet: How to prevent cut and paste data changing references in formulas?

Question

Google Docs has a feature where if you have a reference to a data cell in a formula, and you cut the data from that cell and paste it in a new location, the reference in the formula is updated to point to the new cell location.

I'd like to be able to cut and paste my data without my formulas changing.

I've done some hideous things in the past involving the INDIRECT() function to work round this feature, but it seems like there's got to be an easier way. Any suggestions?

Answer 1

Came across this looking for a similar problem and ended up finding a solution for Excel which seems to work perfectly in the Google Spreadsheets.

For the cell references you don't want to change on paste simply add $ before each part.

So G12 would become $G$12

A cell from a spreadsheet I am using that does what I need, looks something like this:

 =$G$1&Sheet1!B3&Sheet1!A3&$G$2

Answer 2

Work around this the same way as in Excel (I guess someone, somewhere thought this was a useful feature):

COPY and paste the data, and then go back and delete it from the original place.

Answer 3

On mac you can hit function F4 to cycle through the various absolute referencing options when the cursor is on the cell or range.