Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
EugenioB
 
Posts: n/a
Default Unable to Copy COUNTA() with cell references

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
  #2   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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

"EugenioB" wrote in message
...
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. QUEST41067 Excel Discussion (Misc queries) 1 January 15th 05 10:29 PM
Unable to set the active cell from VBA HMS New Users to Excel 1 December 7th 04 10:56 PM
copy a cell value not its function KC Mao Excel Discussion (Misc queries) 2 December 4th 04 05:30 AM
copy paste cell character limit Fred Excel Discussion (Misc queries) 1 December 2nd 04 09:58 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 10:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"