Vlookup on multiple workbooks

First step:
I want to be able to lookup a cell value from workbook1.xls and then find that value in workbook2.xls sheet1.

Second step:
Then, I need to copy the averages (named cells) from workbook1.xls to workbook2.xls sheet1.

So, for example, workbook1.xls has a value of 0016 in cell D5. I press the magic button and it looks at 0016 then switches to workbook2.xls. It looks at column C and searches for 0016 in that column.
After it finds 0016 in column C it then looks back at workbook1.xls and finds the value in "weightavg" (named cell) and copies that value into workbook2.xls column D (to the right of the found cell).

Then looks BACK at workbook1.xls again and finds the value in "thickavg" (named cell) and copies that value into workbook2.xls column E.

This goes on for 10 different named cells of worksheet1.xls.

Any suggestions as to how I might accomplish this?

Ok then... What if these were two worksheets instead of two workbooks? Would it be possible to do what I want if that was the case?

The other way would work for the purpose much easier but if it cant be done that way then I have no choice.

Thanks for any help

A formula can't "push" values - it can only "pull" them from another
cell.

You would thus need formulae in columns D and E of Sheet2 to populate
those cells where the cell in column C contains the value specified in
Sheet1!$D$5 (or [workbook1.xls]Sheet1 if you want to stick with your
original posting).

So, presumably you have a list of values in Sheet 2 column C - assume
this covers C1 to C20. Enter these two formulae in D1 and E1 of Sheet2:

D1: =IF(C1=Sheet1!$D$5,weightavg,0)

E1: =IF(C1=Sheet1!$D$5,thickavg,0)

Copy these two formulae down to row 20.

In your first posting you refer to 10 different named cells - is that 5
lots of "weightavg" and "thickavg", or 10 cells equivalent to D5? If
the latter, then it would be better to group them in a table of 10 rows
by 3 columns and then use a Vlookup formula above.

Hope this helps.

Pete

Thanks for your reply

Using your example it does copy the cells to the correct cells in workbook2.

Now for the problem... I should have mentioned this before but I didn't think about it... workbook1.xls file name will be different every time..

Any way to work around this? Idealy I would like to have a "browse" button where I could tell it which file to import the data from.

Also, if I change the value in cell D5 of workbook1 it does not reevaluate it in workbook2. Seems strange.

Thanks for your help with this.

You could set up a list of possible filenames, let's say in cells L1 to
L10. Then in, say, G1, you can select Data | Validation | Settings and
select "List" in the drop-down of "Allow" - in "Source", point to your
list. When you next click on G1 you will see a pull-down arrow, which
will present you with your list of files - this is effectively your
"browse" button.

You would need to amend both IF formulas and use the INDIRECT function,
whereby you can build up the address you want to refer to as a string -
obviously the filename is in G1 and you will need [ and ] around this
as well as .xls if this is not included with your filenames. You may
also need to include the full path to the file, if it is not in the
currently active folder.

After changing the value in D5 of workbook1, click workbook2 and press
F9 - it should change now.

Hope this helps,

Pete

Ok Im sorry to be such a pain with this because I keep pointing out facts that I should have pointed out in the first place. Your solution would probably work if there were only 20 or even 100 file names to choose from. The thing is I have a different file for each day of the year for each product and there are about 10 products. As you can see this would be quite a list.

Using your solution but with a dropdown list that would show the contents of a specific folder would work. Maybe a dropdown for each product?

Originally I thought this would not be that hard of a problem to solve.. Little do I know!

If, procedurally, you could ensure that your filenames for the products
all had the same structure (and included the date in a pre-defined
way), then the formula which you use to build up the string to pass to
the INDIRECT function would be fairly straightforward. For example,
your list would just have Product_1, Product_2, Product_3 etc to
Product_10, so the selection here would be what appears in G1. Your
filenames would look like:

Product_1_20060114.xls, Product_2_20060114.xls, Product_3_20060114.xls
up to Product_10_20060114.xls,

for 14th January 2006, and for 15th January 2006 they would be:

Product_1_20060115.xls, Product_2_20060115.xls, Product_3_20060115.xls
up to Product_10_20060115.xls.

>From this you could then build up the filename in a predictable manner.
If instead of "Product_1" you wanted a more meaningful name, then you
could have a table of the meaningful names in L1 to L10 with the
numbers 1 to 10 in column M, and use this as a lookup table to get you
1, 2, 3 etc from the chosen names in G1, and then still use the
filenames above. You could have another drop-down to select the date
(or just a single cell where the User specifies the date).

I don't think it's a hard problem - you just need to be able to
describe exactly what you want to do more clearly.

Hope this helps,

Pete

To illustrate how it works, open a new workbook, enter 100 in cell A1,
then use File | Save As to save this file with the name "File1.xls".
Then change the value in A1 to 200 and save this as File2.xls, then
again change A1 to 300 and save as File3.xls, all in the same folder -
close this file.

Open a new workbook and in A1 type 1. Enter the following formulae:

A2: ="File"&A1&".xls"

A3: ="["&A2&"]Sheet1!A1"

A4: =INDIRECT(A3)

You should see 100 in A4. Change A1 to 2 and you should see 200 in A4,
and if you change A1 to 3 you should see 300 in A4.

So, whatever file "number" you put in A1 of this sheet, you will get
the value from cell A1 of that file. A2 and A3 are only showing you how
you can build up the necessary string - you can build this up in one go
in A5 if you wish:

A5: =INDIRECT("[File"&A1&".xls]Sheet1!A1")

So to relate this to your problem, the composite string can be built up
from G1, and instead of

Sheet1!$D$5,weightavg

in the first formula I gave you, you will need two INDIRECT formulae
here to go to the appropriate file and get $D$5 and "weightavg", which
should be defined as a named cell in each of your product files.

Hope this (finally) helps,

Pete

Ok this sounds good and I am going to try it when I get to work but I have a question.
Using the above formulas is it going to save each data from the named workbooks to the one I am placing these formulas in?

I'm still not sure I have given a totally clear view of what I want to do.

Let me try to elaborate a little further.

All the named workbooks product_0016, product2_0016, etc., are individual files with different data in each one. Many of them. Each product in a separate folder. The folders are named for example, product1, product2, product3, etc... The workbook with the formulas you are creating here is only one file which will store the averages of all the product?_????.xls files into seperate worksheets named product1, product2, product3, etc...


In the workbook that these formulas are going into will look like this.

Note: column C below will already have numbers 0016-3656 in them.
Data coming from the first file will be placed in D1, E1, F1, G1, etc...


WORKSHEET 1 (= Product 1 name)
C1= 0016 D1= 3.21 E1= 5.4 F1= 7.38 G1= 12.4 AND SO ON.
C2= 0026 D2= 3.44 E2= 6.1 F2= 5.47 G2= 11.2 AND SO ON.


WORKSHEET 2 (= Product 2 name)
C1 0016 D1 3.65 E1 7.5 F1 9.58 G1 14.5 AND SO ON.
C2 0026 NOTHING because there was no production of this product for this day.
C3 0036 D3 3.54 E3 6.2 F3 6.57 G3 13.2 AND SO ON.
C4 0046

In the above in Worksheet 1 (name of product) C1 will have been extracted from product1_0016.xls. C2 will have been extracted from product1_0026.xls

In Worksheet 2 above the data is extracted from files that were created for
product2_0016, product2_0036, and product3_0036.


All of this data only needs to be evaluated once. After it is in this file it will no longer need to read from the product?_????.xls files again.

I hope I have explained this better. I seem to be having a hard time relaying all the aspects of the task. My apologies.

Yes, you certainly seem to add more detail each time you post. Once you
have your data in these worksheets, you can (and should) fix the
values, by highlighting the cells, then <copy>, then Edit | Paste
Special | Values | OK then <enter>. Then you won't need to refer to any
other files.

Incidentally, I may have misled you earlier - INDIRECT will only work
with files that are open (otherwise you will get a #REF error). So,
open all 10 product files, then the summary file (for want of a better
name) then fix the values in the summary file and save it with a
different name.

Hope this works for you.

Pete

I really do appreciate your time and effort but I don't think this will be a viable solution for the problem.. I will keep working on it though.

Thanks anyway Pete

I think one thing which will help you is if you re-organise your files.
>From what you have said you have 10 different product files each day,
and you want to produce one summary. The product files are kept in
their own folders.

One way would be to put all product files together for each day, so
that you have 10 sheets (plus the summary sheet we have been
discussing) - this can be saved as one composite file with the date
forming part of the filename.

Another approach would be to have only 10 files (one for each product),
but in each file you have multiple sheets - one for each day. Obviously
here the filename would have to include the product description, and
you would have a separate summary file with sheets for each day.

You can copy sheets from one file to another by having two windows open
and using CTRL-drag, or you could move them just by dragging from one
window to the other, so you could re-organise your files fairly
quickly.

Pete