Formulas not calculating automatically

[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

 

[RagDyer]

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

 

[Carla Bradley]

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

 

[Ragdyer]

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.

 

[Carla Bradley]

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

 

[RagDyer]

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