sum

[Guest]

When I try to calculate the sum of special cells in a row, which are copied as link fom another worksheet, the result is zero. I tried to unlock the cells, also confirmed the format of the cells as numbers. But still the result is the same. Then I copied and pasted the values of the cells in a new worksheet but still the result is zero , that should be around 100,000
Can anyone please help me?

 

[Andy B]

Faran

Just because the cells are formatted as numbers does not make them numbers.
Copy a blank unused cell, select the range of 'numbers' and Paste / Special
/ Add. This should make your 'numbers' into numbers!!

Andy.

When I try to calculate the sum of special cells in a row, which are

copied as link fom another worksheet, the result is zero. I tried to unlock
the cells, also confirmed the format of the cells as numbers. But still the
result is the same. Then I copied and pasted the values of the cells in a
new worksheet but still the result is zero , that should be around 100,000!

 

[Guest]

Thanks indeed, Andy
It solved!
But I don't know why it is the first time I encountered this problem, I work with Excel very often. Is it the consequences of linking the cells? I got used to using the sum function just by clicking on the cells , and when I examined them it was correct. If I know the reason about what happened this time I can find which of my works should be reviewed another time.
Bests
Faran

 

[Andy B]

Faran

Linking cells should not make any difference - unless, of course, the cells
you are linking to are not numbers. The best way to check in future is to
make sure that the alignment options (on the toolbar) are deselected. This
will then display numbers on the right of the cell and 'numbers' (text,
really) on the left.

Andy.

Thanks indeed, Andy
It solved!
But I don't know why it is the first time I encountered this problem, I

work with Excel very often. Is it the consequences of linking the cells? I
got used to using the sum function just by clicking on the cells , and when
I examined them it was correct. If I know the reason about what happened
this time I can find which of my works should be reviewed another time.

 

[Don Guillett]

Try this
Sub FixRangeValues()
Selection.Value = Selection.Value
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

When I try to calculate the sum of special cells in a row, which are

copied as link fom another worksheet, the result is zero. I tried to unlock
the cells, also confirmed the format of the cells as numbers. But still the
result is the same. Then I copied and pasted the values of the cells in a
new worksheet but still the result is zero , that should be around 100,000!

 

[Guest]

How can Itry that, Don?

Andy, copy and paste special/ add, doubles the values, what is wrong here?

 

[Guest]

I tried the simple formula:
=a+b+c+d+....n

and it worked!
Yet, I don't know what was the problem
Thanks

 

[Don Guillett]

It's a macro that should be copied into a module.
Then highlight the range to change and execute the macro

BTW Can you copy/paste the formula you used. Should be
=sum(a1:a21) or whatever your range is.

 

[Andy B]

Faran

You need to copy a blank unused cell. Then select the range and Paste /
Special / Add.

Andy.

 

[Guest]

Thanks Andy and Don,
The problem was:
The data were currency, opened from an Access data base in Excel. The cells with the sign of the currency had neithr the characteristics of text nor the number. I couldn't change them into numbers in any case.
I copied and Paste/Add them into a new column then deleted the original column, then I used it.
It is interesting that the original file was Excel( Excel to access to Excel), in which the data are numbers. Therefore when I opened the Access file in excel the format of the cells became solid. Is it corect?

 

[Paul]

Thanks Andy and Don,
The problem was:
The data were currency, opened from an Access data base in Excel. The

cells with the sign of the currency had neithr the characteristics of text
nor the number. I couldn't change them into numbers in any case.

I copied and Paste/Add them into a new column then deleted the original column, then I used it.
It is interesting that the original file was Excel( Excel to access to

Excel), in which the data are numbers. Therefore when I opened the Access
file in excel the format of the cells became solid. Is it corect?
Importing data to Excel (whether from another Microsoft program or
elsewhere) often results in it becoming text.