Moving Rows or Columns In Excel

[hlewton]

“1. Click on the specified column heading or row number to select the entire column or row you need to move. 2. Move the cursor to the edge of selected column or row until it changes to a 4-sided arrow cursor , press and hold the Shift key then drag the selected column or row to a new location.”

I read about this and it said it could move the rows and not leave blank rows without formatting in a table. It seems to work as it says. However, I have this formula, =COUNTA($A$3:$A$20), at the bottom of column A. Even though I have the “$” which I thought meant that Excel is to use only the exact specified cells in the calculation, it changes when I use the above method to move the rows up once a row becomes blank. For instance, 3 rows became blank so I used the above method and moved the effected rows up 3 times. My formula changed to read =COUNTA($A$20:$A$20). This did not happen when I just copied and pasted the rows instead of moving them up.

Shouldn’t my formula do as I want it to even though the rows are moved?

Thanks

[HansV]

The $ symbol means that the reference won't change when you fill down or to the right, or when you copy/paste.
But inserting or deleting cells/rows/columns may change the range the formula refers to, and Excel will update the reference accordingly.
If you want to refer to A3:A20 regardless of whether you move, insert or delete cells in that range. you can use the INDIRECT function:

=COUNTA(INDIRECT("A3:A20"))

INDIRECT takes a string as argument: "A3:A20" in this example. Excel will not adjust text strings in a formula.

[hlewton]

OK, thanks for answering that. I will change my formula.