Workbook will not update after pasting data

[Gina]

I have a workbook where one worksheet holds the raw data the multiple other
sheets reference in calculations. I left this workbook empty as I pulled out
and cleaned the data that would need to be pasted in later.

None of the calculations that need to take place take place in this
data-only sheet.

So this morning I went and pasted in all of the data, once I'd made sure
that it was clean and met all of the data validation criteria set up in the
worksheet. But when I went to look at the calcualtions in the other
worksheets that were based upon this data, they were all "0" or blank.

However, if I type the exact values over the data I pasted in. For
instance, if I type 175.00 over the existing 175.00, suddenly the formulas in
the other workbook pages that used that value as a calculation "see" the
value and begin to work.

There are at least 600 rows with 10 fields that have calculations based on
them that I pasted into the "data" sheet this morning. Is there an easier
way to get the calculations in other worksheets to recognize these fields
than to manually type over them?

Thanks,
Gina

 

[Spiky]

You are probably pasting Text, and Excel sees it that way. This could
be a simple formatting issue, or there could be spaces or other non-
visible characters in the pasted numbers.

If it is formatting only, just select the whole space and format to
General or a number format.

If there are extra spaces or other characters, do a Replace to get rid
of them. You can go into a cell, highlight and Copy the offending
characters, then Paste them into the Replace dialog box in the "Find
What" field and click Replace All. (leave nothing in the "Replace
With" field) You may also have to change the formatting after this. If
you do this frequently enough, you may want to record this series of
actions in a macro to repeat more easily.

 

[Gina]

You put me on the right track. I typed "1" into an empty cell, and then
multiplied via "paste special" 1 by all of the numbers that were not working
correctly, adn voila, problem solved- at least for the numeric fields..

Thank you.