Excel Values problems

  • Thread starter Thread starter sbenson
  • Start date Start date
S

sbenson

I'm opening an exported Crystal Reports excel file in Excel.

The file opens properly with no problems other than cell values are not
being treated as cell values. Selecting several cells with numbers in
them gives no result in the status bar area.

If I press F2 (to edit a formlua) and then enter the value is treated
as a number.

As I have thousands of cells like this, has anybody got any ideas of
why this is happening and what I can do to fix this.

I have formulas that depend on cells being treated like numbers.

Regards

Sam Benson
 
Hi

It seems you have data formatted as text there. Simply setting the format
for range to general or number etc. isn't enough. Two possible solutions for
you there:

1. Reset the range format. Enter number 1 into some free cell, and copy it.
Then select the range, and then PasteSpecial.Multiply.
Or
2. In your formulas, embedd all references to mentioned range(s) into
VALUE() function - something like
=SUM(VALUE(A2:A100))
(I'm not entirely sure, can you use VALUE() on range or not, but you can try
anyway)
 
This has been a problem with Crystal Reports excel files for years.
Crystal exports numbers to Excel as text. Perhaps yet another complaint
to them would be in order; maybe some day they will fix this proble.

In the interim, Arvi's instructions for converting text to numbers
should do the trick.

Jerry
 
Back
Top