If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Vlookup not working to merge workbooks?
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/comm...et.f unctions |
#2
|
|||
|
|||
Vlookup not working to merge workbooks?
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/comm...et.f unctions |
#3
|
|||
|
|||
Vlookup not working to merge workbooks?
Thanks that worked
Heather "Billy Liddel" wrote: 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/comm...et.f unctions |
Thread Tools | |
Display Modes | |
|
|