Vlookup on multiple workbooks

[clayton]

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

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

So, for example, workbook1.xls has a value of 0016 in cell D5. I pres
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 an
finds the value in "weightavg" (named cell) and copies that value int
workbook2.xls column D (to the right of the found cell).

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

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

Any suggestions as to how I might accomplish this

 

[clayton]

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

 

[Pete]

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

 

[clayton]

Thanks for your reply

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

Now for the problem... I should have mentioned this before but I didn'
think about it... workbook1.xls file name will be different ever
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 no
reevaluate it in workbook2. Seems strange.

Thanks for your help with this

 

[Pete]

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

 

[clayton]

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

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

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

 

[Pete]

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

 

[clayton]

I am kinda getting lost trying to make the indirect function work. Ca
you give me an example

 

[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

 

[clayton]

Ok this sounds good and I am going to try it when I get to work but
have a question.
Using the above formulas is it going to save each data from the name
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 t
do.

Let me try to elaborate a little further.

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


In the workbook that these formulas are going into will look lik
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 fo
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 bee
extracted from product1_0016.xls. C2 will have been extracted fro
product1_0026.xls

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


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

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

 

[Pete]

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

 

[clayton]

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

 

[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