RE: Vlookup not working to merge workbooks?

[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" wrote:

> 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/community/en-us/default.mspx?mid=2baf3485-6735-4505-a3a9-fcee0d0e57b1&dg=microsoft.public.excel.worksheet.functions

[Heather]

Thanks that worked
Heather