Is there an easier way to make cell references absolute? (Answered: Use Relative references.)

[lonadar]

I’m working on a spreadsheet that’s a sort of time table.

Item / Amount of time it takes / calculated time once done.

I do a lot of cutting and pasting of these items as I rearrange the schedule.

Whenever I cut an item and paste it elsewhere in the schedule, I’d like the times to recalculate. However, because I’m cutting and pasting cells, all of the cell references in the “calculated time since done” column change as well. Perhaps I’m missing something but if I put in absolute cell references, then I can’t just Fill Down the sheet because they’ll all look at the singular cells from the initial entry. If I only use partial absolute cell references, then all the others will adjust, and there are enough cell references in the calculation I’m doing in that column that it’d take me over an hour just to add all the $'s.

Is there a way to make it so cell references don’t follow when I cut and paste without having to mark them all “$”? I want to be able to Fill Down and have them auto-update in series, but I don’t want them to follow the data if I move it.

[erAck]

It’s expected spreadsheet behaviour that cell references pointing to the moved cell are adjusted for a cut&paste. If you don’t want them to be adjusted then either use copy&paste instead and delete the source cell afterwards, or use named expressions / ranges containing a relative cell reference, those are not adjusted to move along but always point to the relative position where used. For example (with Ctrl+F3) on B1 define a name leftcell and assign it the reference A1 and then in B1 enter the formula =leftcell. When using the same formula in another cell it references the cell left of that cell. You can also use a mix of absolute and relative cell addressing in the named range, like $A1 or A$1 or even $A$1, references in a named range are only adjusted if all parts, sheet, column and row, are absolute, e.g. $Sheet1.$A$1.

[lonadar]

Excellent! I wasn’t aware of relative references, That appears to have done the trick. On a side note - on a Mac it’s Cmd-F3, not Ctrl-F3. Thank you!