Vlookup not working to merge workbooks?

[Heather]

HI,
I have 8 workbooks, each with one work sheet containing an identical column
A then the next three columns in each contain unique data that I want to
combine from all 8 based on column A.

I'm using the following formula:
=VLOOKUP($A1,'A2 AlleleReport.XLS'!$A$1:$D$100,1,TRUE)

Although I've opened and linked all the files using connections it doesn't
seem to be working? Initially Excel opens up a box for me to browse where the
linked file is (once I've put the formula in the first workbook), I click it,
but then it refers to $A$1 of my 1st workbook to take the reference from, not
the second workbook, and doesn't even try to bring in the data from columns
B-D?

Please show me what I'm doing wrong?
To complicate matters I have to work with Excel 97-2003 compatible workbooks
in Excel 2007 = could this be a problem with linking the files?

Thanks for any help you can provide...NB:I can't do VBA.
Heather

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Billy Liddel]

The columns problem can be solved using the COLUMN() function.
Replace =VLOOKUP($A1,'A2 AlleleReport.XLS'!$A$1:$D$100,1,TRUE)
with
=VLOOKUP($A1,'A2 AlleleReport.XLS'!$A$1:$D$100,COLUMN(),TRUE) in say B1
you should be able to copy the formual accross to get the other data.

to refer to another workbook in say column E use

=VLOOKUP($A1,'A2 AlleleReport2.XLS'!$A$1:$D$100,COLUMN()-3,TRUE)

and copy this to col g.
'A2 AlleleReport2.XLS' is the name of your second workbook.

You will need to build up the formulas refering to each of the 8 workbooks
to build the entire table.

Hope this gets you started

Peter

 

[Heather]

Thanks that worked
Heather

The columns problem can be solved using the COLUMN() function.
Replace =VLOOKUP($A1,'A2 AlleleReport.XLS'!$A$1:$D$100,1,TRUE)
with
=VLOOKUP($A1,'A2 AlleleReport.XLS'!$A$1:$D$100,COLUMN(),TRUE) in say B1
you should be able to copy the formual accross to get the other data.

to refer to another workbook in say column E use

=VLOOKUP($A1,'A2 AlleleReport2.XLS'!$A$1:$D$100,COLUMN()-3,TRUE)

and copy this to col g.
'A2 AlleleReport2.XLS' is the name of your second workbook.

You will need to build up the formulas refering to each of the 8 workbooks
to build the entire table.

Hope this gets you started

Peter