Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) | |||
Unable to set the active cell from VBA | New Users to Excel | |||
copy a cell value not its function | Excel Discussion (Misc queries) | |||
copy paste cell character limit | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |