Unable to Copy COUNTA() with cell references

[EugenioB]

In sheet A I have a number of columns, only partially full of cell entries.
The cells without entries are simply blank.

Lower down in sheet A, below the bottom of these columns I have a 2-column
summary table with rows listing total number of cell entries in certain
specific columns above. The counting of the number of cell entries in each
column is done through the function COUNTA(), with range specified as the
full height of the relevant column. This works fine.

In sheet B I also have the same pattern of columns, with identical headers
and formats, but different cell data. I want the same summary table of column
totals at the bottom of this data set in sheet B as I already have in sheet A.

I go to sheet A, copy the entire summary table and paste it into sheet B, in
a similar or identical position as the original one in sheet A. All is copied
correctly, except that each one of the cell references for the COUNTA()
function show up as #REF! and the result displayed in the cells is 1, -1 or 0.

Cannot figure out why.
Found nothing useful in Help or by searching through this Community.
(Using Office2003).

Anybody have any ideas ?

TIA

 

[Bernie Deitrick]

Eugenio,

That happens if you shift the columns off the sheet by not pasting into
exactly the same column as before. For example, if you have the formula

=COUNTA(A:A)

in cell H1, and copy and paste it into a cell in column G, then the A:A will
turn to #REF since there is no column to the left of A.

You can cure that by using

=COUNTA($A:$A)

or by pasting into the same column as you copied from, then dragging the
formulas where you want them to appear.

HTH,
Bernie
MS Excel MVP