Help with multiple workbooks

[Guest]

Good day. I am using Excel 2002 on Windows XP. I have several workbooks
controlling the production in my ice cream store.

1. Item Price List - stores the cost of each component used in a product
with data supplied from Quickbooks. This workbook also calculates the cost
per ounce or pound since Quickbooks cannot do this.

2. Product Cost - calculates the cost of each product sold by listing the
components of the product and using VLOOKUP to find the price from the Item
Price List. One workbook per product.

3. Master Price List - calculates the sales price of each product using
data from the Product Cost workbook. Assembles all of the products sold in
one worksheet.

The problem is this: When I open the Master Price List and look at the cost
of each product, it appears to be finding an old data. If I then open the
particular Product Cost worksheet and confirm the current cost, the price
then changes in the Master Price List.

I currently have over 100 products that I sell in the store, each one having
its own Product Cost workbook. I have examined the Links and all appear to
be correct. Is there something I am missing? Is there an update function
that I am missing? When I opened the Master Price List, it asked if I wanted
to Update the values, I answered Yes. Even if I exit and restart Excel and
answer Yes to Update again, the same old values are shown.

If more details are needed, I will gladly supply via email.

Thanks in advance, Danno...

 

[Guest]

How do you do step # 3

What do you use to get product cost into MAster

I assume calculate is on automatic.

PS: Depending on the size of the 100 individual workbooks... have you looked
at using sheets in the master instead of 100 files?

 

[George Nicholson]

Links read the last saved data in a file (unless the file is open, in which
case it reads the "current" data). Updating ItemPriceList workbook won't
change the calculations in a non-open ProductCost workbook until you open it
(generally, Excel recalculates a file being opened).

If MasterPriceList gets it's data from calculations in ProductCost, you
won't see changes in MasterPriceList until ProductCost has been given a
chance to recalculate. Recalculation won't happen on closed files (it would
be one hell of a trick for this to be otherwise if you think about it).
Links to closed files only show the results of the last calculation
performed before the file was last saved.

HTH,

 

[Guest]

Good day George and thank you for the quick and accurate response. Your
explanation was concise and to the point. It leads me to the obvious next
question. Is there a way to create a routine or macro to exercise each of
the 100 Product Cost workbooks to get the calculation to be current. And
then, after that, will the Master Price List pick up the new calculated data?

Thanks again, I can always count on this group for answers. Danno...

 

[Guest]

Good day "Vacation's Over" and thank your the quick response. The Master
Price List looks at the cost for each of the 100 products in the Product Cost
workbook and then does calcuations for mark-up, round off for taxes, etc. I
am not aware of the "Auto Calculate" function. I will take a look and see if
I can find it.

Thanks, Danno...