$ and moving the cell not the formula

[lezawang]

Hi
I fixed $J$1 in the formula and accidentally moved J1 (dragged and drop it somewhere else), the formula below will update $J$1 to the new location for example $L$20. I thought $ will fix the address? I know I am not moving the formula but still? Thank you so much.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]date[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

J10 =COUNTIF(C2:C30,$J$1)

 

[mikerickson]

If you drag and drop or Cut/Paste a range all formulas referencing that cell will adjust to the new location of the cell.

The $ determines how the formula will act when copied to other cells (or fill downed or ....).
But when a cell is moved, all formulas referring to that cell are adjusted to track the cell's new location.

"Don't move J1" is the best advice, but if you absolutly have to and still want the formula to refer to the new J1, use INDIRECT

=COUNTIF(C2:C30, INDIRECT("J1"))