Formula recalculation

[Dan Wilson]

Good day. I am using Excel 2002. I have several
worksheets that contain product recipes. Each of the
worksheets uses VLOOKUP to get item costs from a master
price list worksheet. There is also another worksheet
that uses VLOOKUP to total and average the costs of all
the individual product recipe worksheets.

Up until tonight I thought that everything was working
well. I just discovered that if I change the basic cost
of an item, the total and average worksheet is not
updating automatically.

If I examine the master price list, all is well. When I
examine any individual product recipe, all is well and the
new prices from the master price list are calculated.
When I examine the total and average worksheet, the costs
shown are from the previous price list. If I open the
product recipe while the total and average worksheet is
open and then close the product recipe, the new costs are
reflected. There are over 60 product recipe worksheets
and I don't want to have to open each one while the total
and average worksheet is open to have the updated costs
calculated. Especially since my costs change frequently.

Is there a recalculate function or something that will fix
this problem? Is there something that I am not doing
correctly? I am willing to provide more details via
private email if necessary.

Thanks, Danno...

 

[Dan Wilson]

Good day. This is additional data for the problem
described below. I have discovered that by opening and
saving the changes calculated in each of the individual
product recipe worksheets, the new costs are then
reflected in the total and averages worksheet.

So, since all of my product recipes are in one folder on
my computer, is there a way to write a macro that will
open and save each product recipe?

Thanks, Danno...

 

[Frank Kabel]

Hi Dan
does hitting F9 change the values?

 

[Charles Williams]

Hi Danno,

The reason for your problem is that (as i think you have already discovered)
closed workbooks do not get updated.

Yes you can write a macro to open and save each product recipe: you can use
the DIR function to get the file names and Workbook.OPEN and workbook.save
to open and save the books.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Dan Wilson]

Good day Frank, thanks for the response. In further
investigation, I have found that the problem appears to be
with the product recipe worksheets, not in the total and
average worksheet. Once the recipe is updated, the total
and average worksheet sees the new values.
I looked through the Help file, but could not find
anything on the F9 key. I will have to look in the user
guide.
Thanks, Danno...