Possible Paste Excel Bug?

[boyhood_bravery]

Hi All,

I have scanned this forum a few times over the past few weeks and have found lots of handy tips to help me with a spreadsheet I've been working on for a Football Predictions League that we do at work. I am hoping that someone may be able to give me a bit of help with an issue I have before I tear my hair out!

I got all my formulas working to correctly workout the results for our weekly footy predictions, I won't bore you with the details of our game. Anyhoo, I tested all the formulas quite extensively, using a number of different scenarios, and it worked fine.

I email the fixtures out and then each player emails me back their predictions for the week. I have noticed that when pasting (paste special > values) the data into the spreadsheet, it seems to mess up the formula so that the points the player has earned are not calculated correctly. However, if I then type these values - or type over the values I've placed in the cell - into the cell then the formula works like a dream!

It's nothing to do with the formatting, as I've made sure everything is of the same format, however that shouldn't make a difference anyway; As I'm only pasting values.

Is anyone aware of this bug and, more importantly, is anyone aware of a fix or a workaround that would save me having to manually key in 432 values every week?

Thanks in advance for any help/info that you guys and gals can share,

Craig E. Cub

ps- I've done all the usual google searches but cannot seem to pinpoint the exact error/bug that I am trying to find.

pps - I am using Excel 2003.

 

[Richard Schollar]

Hi Craig

Sounds to me like Excel thinks these numbers are in fact text and hence is not summing them/performing whatever mathematical operation on them. Assuming they are all in the same column (or spread over just a few columns), just select a column (do it for each column individually) and go Data>TextToColumns>Finish. This should convert them back to proper numbers and sort your formula values out.

 

[boyhood_bravery]

Richard,

Thanks for your swift reply. I'll give it a go now and let you know the results. I would have thought the paste special would have combatted this problem though?

Craig

 

[boyhood_bravery]

Hi Richard,

Unfortunately that didn't seem to fix it

It's really frustrating, I know I can fix it by just entering the values but it kinda strips the point of me being able to paste in the data.

Anymore ideas, Richard? What about anyone else? Has anyone else came across this before and if so, what did you do to fix it?

cheers,

Craig

 

[Richard Schollar]

Another thing to try is type a 0 in an empty cell somewhere, select these values again and then go Edit>PasteSpecial>Operation:Add and see if this converts them.

 

[boyhood_bravery]

No, no joy there, Richard.

I have tried laods of different things. Such as, what if i paste my data into the same workbook but a different worksheet and then paste into the formulas that didn't work. Also tried pasting the data into the same worksheet and then copying from there into forumula, again to no avail.

It's really frustrating , as it shouldn't make a difference as whether the values are pasted or keyed, the values are the same!

 

[Richard Schollar]

Have you examined the data in the cells that you are copying from? They don't contain what might look like leading or trailing spaces do they?

 

[boyhood_bravery]

Think you've hit the ol' nail on the head, Richard! Muchos Gracias!

Any idea of a quick way of ensuring that all the data has now trailing spaces before I paste it into the cells used to workout the formula?

 

[Richard Schollar]

You could just do a Find/Replace in the destination cells with a Find of " " (without the quotes!) and Replace of {leave blank} and hit Replace All. Hopefully that will serve to 'convert' the data to proper values.

 

[boyhood_bravery]

Richard, it didn't do it when I did a normal space, but when I did this (as found on another site)...

"It could be a special character that appears as a space. Try
Search/Replace. In the search line, hold down the Alt key and hit 255 on the
number keypad. Then hit Replace All."

...It worked!

awesome!

Cannot thank you enough for all your help.This forum is ace.

Craig