# search value in multiple workbooks

#### freezefiz

##### Board Regular
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?

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### iliace

##### Well-known Member
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

##### Board Regular

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

##### Well-known Member
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

##### Board Regular
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?

pls help.

thanks.

#### iliace

##### Well-known Member
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.

Replies
9
Views
126
Replies
1
Views
255
Replies
3
Views
320
Replies
14
Views
366
Replies
3
Views
306

1,191,587
Messages
5,987,508
Members
440,098
Latest member
MickyMouse123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back