Cells not calculated

[Ian]

I have a spreadsheet that is output from an Access form.

H7 (General format) holds =SUM(H2:H5). This is right-justified.
Autocalculation is on
H2 to H5 (General format) hold what appears to be numbers and is
left-justified.
H7=0
If I reformat H2 to H5 as number with 2 decimals, the appearance doesn't
change and H7 remains at zero.
If I double click on eg H3, then click elsewhere, H7=H3.

So it appears that the numbers in H2 to H5 are actually text.

This all used to work, but some changes have been made to the database and
now we have this problem. If I can't find the root cause (ie why Access it
outputting in this way), how can I programmatically change the values in
column H into numbers?

Ian

 

[Niek Otten]

Hi Ian,

You can programmatically mimic the manual solution:

Format an empty cell as number (just to be sure)
Edit>Copy
Select your "numbers"
Edit>Paste special, check Add

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I have a spreadsheet that is output from an Access form.
|
| H7 (General format) holds =SUM(H2:H5). This is right-justified.
| Autocalculation is on
| H2 to H5 (General format) hold what appears to be numbers and is
| left-justified.
| H7=0
| If I reformat H2 to H5 as number with 2 decimals, the appearance doesn't
| change and H7 remains at zero.
| If I double click on eg H3, then click elsewhere, H7=H3.
|
| So it appears that the numbers in H2 to H5 are actually text.
|
| This all used to work, but some changes have been made to the database and
| now we have this problem. If I can't find the root cause (ie why Access it
| outputting in this way), how can I programmatically change the values in
| column H into numbers?
|
| Ian
|
|

 

[Ian]

Thanks, Nick. I hadn't realised that this could be done. I had assumed that,
as the values appear to be numbers, then changing the format to number
should be sufficient. It obviously wasn't and your solution has worked (at
least manually).

I'm still hoping I can get the correct output from Access in the first
place, but otherwise implementation of this method in code will be an easy
fix. In fact, I might implement it anyway for ALL the numeric values in the
sheet, as it isn't going to cause any problems with "working" values anyway.

Ian