Cell References changing despite $ being used in the formulas.

[CrazyDave]

G'day experts,

I'm running Excel 2013 (haven't managed to afford an upgrade...).

Putting $ into a cell reference ($A1, A$1, $A$1) locks that cell reference down in that formula, no matter where you take the formula, right?

Is it supposed to also keep the cell reference locked down when you edit the cells being referenced?

I've got a sheet where I have 10 tables, one for each of Monday AM, Monday PM, Tuesday AM, and so on to Friday PM.
The table for Monday AM is A3:B33. Then there's a column blank, and the table for Monday PM and another blank column, and so on and so forth.

Within each table, are three sections of 9 rows each, which I need to know (without manually counting) how many of those 9 rows have a blank cell... so, underneath the tables, I have these three formulas -
=9-Countblank(A4:A12)
=9-Countblank(A14:A22)
=9-Countblank(A24:A32)

This information also gets used elsewhere within the document, so it's handy to have Excel doing it's job so I don't have to manually transfer the counting...

When editing the information in a table, I often cut and paste from one of the groups of nine to another (so I'll transfer data from the first group of nine to the second, so I can put other data into the first group of nine). When I do this, I often have to go and edit one or more of the three formulas, as the cell references have changed.... quite frustrating....
So I locked the cell references down, with the $.
Example - =9-Countblank(A$4:A$12)
And they still change...

Is this a glitch in Excel? Or am I doing something wrong?

Many thanks in advance

 

[FormR]

Is this a glitch in Excel? Or am I doing something wrong?

Hi, neither really - if you cut and paste all 9 cells to another location any formulas exclusively referencing all of those 9 cells will update - this is intended behavior.

Here is an alternative you could try that would only update if you cut and paste the whole column.

=9-COUNTBLANK(INDEX(A:A,4):INDEX(A:A,12))

 

[CrazyDave]

Hi, neither really - if you cut and paste all 9 cells to another location any formulas exclusively referencing all of those 9 cells will update - this is intended behavior.

Here is an alternative you could try that would only update if you cut and paste the whole column.

=9-COUNTBLANK(INDEX(A:A,4):INDEX(A:A,12))

FormR, Mate! This is genius!
Many thanks! You've literally saved me time from having to correct dozens of formulas referencing cells in this document. The ones I mentioned are just the tip of the iceberg.
Now I just need to figure out, hopefully it's easy to do, how to apply this new way to the other formulas...
Thanks again mate. I'm sitting still, but I'm bouncing around inside...

Oh... for anyone else using this formula... FormR did miss an opening bracket, but I ain't holding that against him... Just need to put an opening bracket in front of the 2nd 'index'.