formulas not auto calculating and will not calculate

T

tazatha

an excel spreadsheet created by fox pro 9.0 will not calculate the formulas.
They appear as the formula such at =H2*(100+O2)/100
clicking on the cell will not allow it to recalculate. rekeying the formula
some where else in the spread sheet will not calculate. if i copy the lines
from the spreadsheet and put them in a blank spread sheet it will not
calculate. if i retype the formula in another cell farther down the new
spreadsheet and hit enter. the formula will calculate. what is getting set
or changed or what? to prevent formulas from calculating?
 
R

Rick Rothstein

What do you see in the cell (not the Formula Bar)? What is the Cell Format
of the cell containing the formula?
 
D

Dave Peterson

It sounds like the cell may be formatted as Text

In xl2003 menus:
Select the cell(s)
Format|Cells|Number tab
choose General or number (or anything but Text)
Then select the cell, hit F2 (edit key), and hit enter (done editing)

Or you could be looking at formulas--instead of the results:
Tools|options|view tab|uncheck Formulas
(ctrl-` (ctrl-backquote, the key to the left of the 1/! on my USA keyboard)
is the shortcut key
 
T

tazatha

Partially answered the question for my circumstances. I did not have
formulas checked to be shown, even formulas that evaluated to text are not
working. I did not have to change the type - number, text, general, but what
i did have to do was your suggestion to click on cell and hit F2, then enter.
that made the value show up instead of the formula.

The spreadsheet created by the fox pro program previously created the sheet
with values showing automatically. There are too many cells to have to ht f2
and enter on each one.

Any ideas on what i can do to the whole spreadsheet to get it to show values
inistead of formula?
 
T

tazatha

i see the formula format both in the cell and in the formula bar. if i hit
F2 then enter, as dave p recommended, the calculation takes place as it
should. there are too many cells to do this on each spread sheet generated.
 
D

Dave Peterson

Select the range to fix
Change the range's format to Gneral (or whatever you want)
Edit|replace
what: = (equal sign)
with: =
replace all

Excel will see that each is a formula and reevaluate each formula.
Partially answered the question for my circumstances. I did not have
formulas checked to be shown, even formulas that evaluated to text are not
working. I did not have to change the type - number, text, general, but what
i did have to do was your suggestion to click on cell and hit F2, then enter.
that made the value show up instead of the formula.

The spreadsheet created by the fox pro program previously created the sheet
with values showing automatically. There are too many cells to have to ht f2
and enter on each one.

Any ideas on what i can do to the whole spreadsheet to get it to show values
inistead of formula?
 
R

Rick Rothstein

You can convert all the cells to real formula very quickly using VB. First,
select all cells with the problem, change the Cell Format to General and,
with the selection still active, do the following... right click the tab at
the bottom of the worksheet, select View Code from the popup menu that
appears to get into the VB editor, then copy/paste the following into the
Immediate window (press Ctrl+G if it is not displayed by default)...

Selection.Formula = Selection.Value

then, with you text cursor in the above line, hit the Enter key. Now, when
you go back to the worksheet, all the selected cells should now be active
formulas.

--
Rick (MVP - Excel)


tazatha said:
Partially answered the question for my circumstances. I did not have
formulas checked to be shown, even formulas that evaluated to text are
not
working. I did not have to change the type - number, text, general, but
what
i did have to do was your suggestion to click on cell and hit F2, then
enter.
that made the value show up instead of the formula.

The spreadsheet created by the fox pro program previously created the
sheet
with values showing automatically. There are too many cells to have to ht
f2
and enter on each one.

Any ideas on what i can do to the whole spreadsheet to get it to show
values
inistead of formula?
 
T

tazatha

thanks so much. that is much simpler way to get it to calculate. I can get
this to my users now so they can get the data to calculate quickly. Now the
programmer just has to figure out what he did in fox pro to make the program
set up the excel spread sheet like this since it just started happening on
his last version of the export.
 
T

tazatha

what do i do to return and have it work? when i hit enter on the vb editor
then go back to spread sheet. still the same. if i close the editor and
return same ugly formula. thnx for the help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top