search value in multiple workbooks

[freezefiz]

hey ppl..

i've a worksheet of products whereby the quantity will have to be retrieved from various workbooks.

eg.

colA---------colB
carrot--------QTY?
tomato-------QTY?
cabbage------QTY?

is there a particular formula or method that i can use for this?

 

[iliace]

You would use a worksheet function as you normally would within a single workbook, except address an external workbook to get its quantities. The simplest way to do this is have the other workbooks open, and when you enter your formulas, highlight the data ranges of the other workbooks. Once the other workbooks are closed, your formulas will be updated to address them correctly.

 

[freezefiz]

hey thanks for replying..

i'm sorry.. i don't get what you mean..
i'm kinda a beginner here.. sorry abt that..

issit possible if you could show some examples for the formulas?

 

[iliace]

Here is what I am explaining. Suppose you have two files - one on your desktop, another in some place on you hard drive. You want to get into File 1 and sum some of its data. Here I have book1.xls and book2.xls, and from book2 I do a sum on book1's data:

Now, here is what happens after book1.xls is saved and closed. Observe how the "link" is preserved in book2.xls:

I hope that was what you were asking.

 

[freezefiz]

well..yes, the ultimate mission is to compile the total.
but unfortunately my quantity values isn't from a range(eg colA) as shown in your example.

but thank you for taking your time to explain it.

What i have is MULTIPLE workbooks with this format :
CustID------customer--------carrot-----tomato------cabbage
001-----------SHOP1-----------3----------5-----------5-------
002-----------SHOP2-----------1----------1-----------2-------
003-----------SHIO3------------5----------0-----------0------

And my Summary(in another wbk)format is :
product-----------quantity---------
Carrot-------------??---------
tomato------------??---------
cabbage-----------??---------

In other words i need to retrieve & sum it all up for every single product by searching through all the wbks that is in a particular folder "LOGISTICS"

Which direction should i go to with this problem?
use SUM IF? SUMPRODUCT?

Sorry but i'm really clueless about this.

pls help.

thanks.

 

[iliace]

What you can do is have a macro go through that folder, find the files you need, and build the formulas. Here are the questions you need to answer:

Is the data always in the same columns, in each file?
Or, is the column header always in the same row?
Do you want to go through all the files, or only certain ones?
Will any of the files have new data, or have data deleted?

If you'll be adding new files, you'll also need to re-run the macro to include them in your formula.