Formulas not calculating automatically

  • Thread starter Thread starter Carla Bradley
  • Start date Start date
C

Carla Bradley

I'm not a very advanced Excel user, but I have a client
that I have to find a solution for. She has a spreadsheet
and is using the vlookup formula in several cells.
However the cells are not calculating automatically. I
checked the tools, options, calculations tab and it's set
to automatic. I even tried to press F9 and nada. However
she says that it will do that and them sometimes hours
later it'll just automatically calculate even though she
hasn't done anything. I have no idea where to go from
here. Any suggestions?

Oh and here is an example of one of her formulas. I'm
ignorant to this one. She does have a sheet1 with various
numbers in those cells.
=VLOOKUP(B1,Sheet1!$B$2:$J$3215,8,0)

Thanks,
Carla
 
It sounds like maybe there are a lot of big formulas!

Have her look in the status bar (above Windows Start) and see if it says
"Calculate".

If it does, that means the sheet is *calculating*, which means that she must
have a great many large formulas that do, perhaps, take a long time to
complete their assigned tasks.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I'm not a very advanced Excel user, but I have a client
that I have to find a solution for. She has a spreadsheet
and is using the vlookup formula in several cells.
However the cells are not calculating automatically. I
checked the tools, options, calculations tab and it's set
to automatic. I even tried to press F9 and nada. However
she says that it will do that and them sometimes hours
later it'll just automatically calculate even though she
hasn't done anything. I have no idea where to go from
here. Any suggestions?

Oh and here is an example of one of her formulas. I'm
ignorant to this one. She does have a sheet1 with various
numbers in those cells.
=VLOOKUP(B1,Sheet1!$B$2:$J$3215,8,0)

Thanks,
Carla
 
Unfortunately it doesn't. It says ready. I think there
must be something wrong. I've had the same document open
on my computer for hours and it's never changed from
showing the formula. I read where you can hit ctrl + ` to
toggle between formulas and their values and when I do
that other rows change, but this one with the vlookup
formula does not. UGH! Thanks for trying.

Carla
 
You stated that the formula might "calculate" hours "later".
You didn't mention before that you could *see* the formula *itself* in the
cell.

That could mean a couple of things.
One of which, is that the cell containing the formula was formatted to text.
If that's the case, the cell has to be formatted to "General", *then* the
formula has to be re-entered or edited before it can "calculate".

To see if this is the issue, select a cell containing a visible formula,
then,
<Ctrl> <Shift> <~> (shortcut for formatting to General),
Then <F2> <Enter>, and see if this works.

If it *doesn't*, see if there is the possibility that there is a "space" in
front of the formulas' equal sign.
 
Sorry I wasn't more clear in my first post. It is showing
the formula and not the formula's value. The client
claims that after awhile it will calculate. However I
left it up on my machine for 24 hours and it never even
attempted to calculate.

She did have some columns labeled as text. I changed all
to general. The I deleted and added back the formula, but
it still shows the formula and not the forumla's value. I
checked for spaces and there were none.

I tried the ctrl + shift + ~ then F2 and enter. It
changed to #N/A. I used the smart tag to show calculation
steps and I'm not really sure what I'm looking for but
after hitting the evaluate button it came up with #N/A
again. So am I safe to assume there is an error in her
formula?

My boss HAS to send me to Excel training if I'm going to
support it. Thanks a million for all your help thus far!

Carla
 
You're absolutely correct !

The #N/A OR *any* other error message, does mean that the formula is *now*
calculating.
What's casusing the error messages is a whole other issue.

The best procedure to follow to correct the existing "Text" formulas, now
would depend on *how many* formulas are involved.

If you're talking 5 or 10, you could just first, select them all, then,
While they're all still selected, hit <Ctrl> <Shift> <~> ONCE,
to change all the cells' formats at one time, then repeatedly hit <F2>
<Enter>, which will automatically go to the next cell selected, to do the
final edit.

If you have a great deal more, post back for other options.

Of course, you now will have to address the error issue.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Sorry I wasn't more clear in my first post. It is showing
the formula and not the formula's value. The client
claims that after awhile it will calculate. However I
left it up on my machine for 24 hours and it never even
attempted to calculate.

She did have some columns labeled as text. I changed all
to general. The I deleted and added back the formula, but
it still shows the formula and not the forumla's value. I
checked for spaces and there were none.

I tried the ctrl + shift + ~ then F2 and enter. It
changed to #N/A. I used the smart tag to show calculation
steps and I'm not really sure what I'm looking for but
after hitting the evaluate button it came up with #N/A
again. So am I safe to assume there is an error in her
formula?

My boss HAS to send me to Excel training if I'm going to
support it. Thanks a million for all your help thus far!

Carla
 
Back
Top