Deleting underlying formatting of cells which had formulas

[drewsta]

Hi all,

I have a problem in that when I copy and paste values from a block of cells (which had formulas in them) with paste special - values only in to another set of cells with formula the values are incorrectly calculated. It is obvious that the cells I have copied still have some underlying reference or formatting that impedes the latter formula calculating correctly. If I physically overwrite (type) the cells with the same value and paste it in to the new cells - it works. I have also tried copying and pasting the values in to a new sheet first then (paste special - values only) and then copying that and re-pasting (values only) them in to the new cells with no success.

Any help is most welcome.

Cheers,

Drew.

[millimo]

Without a sample sheet showing your formulas it is difficult to guess what the problem is.
The only thing I can immediately think of is that where you are pasting the data some of the cells might have different formatting attached to them.
Check this or please post a sample sheet for us to look at.

[newdoverman]

A quick guess is that the cells that you are copying have what look like numbers but are really just text that look like numbers. This can be the result of formula like this =LEFT(D1,1) which, if started with a number will return a value that looks like a number but is really a text value. This would cause formulae addressing the cells where the pasting took place to fail if a numeric value was expected. To fix this, enter a 1 in a cell out of the way and copy it. Select the cells that you Pasted Special, click on Paste Special, select MULTIPLY and click OK. Delete the 1 that you entered.