Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup multiple workbooks
I am attempting to use VLOOKUP to link 2 workbooks together.
This formula works fine: =VLOOKUP(A4,[test1.xls]Sheet1!$A$4:$A$1000,1,FALSE), However, when I try to copy this formula into another workbook where the workbook name has a space in it, i.e., test 1, I get this error message: Name invalid. Any Suggestions?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup multiple workbooks
If you build your formula by typing:
=vlookup(a4, then use Window (on the worksheet menu bar) to select the other workbook then go to sheet1 of that other workbook and point at the range (a4:a1000), you'll see that excel will add some apostrophes. =VLOOKUP(A4,'[test 1.xls]Sheet1'!$A$4:$a$1000,1,FALSE) I've found that excel does a much better job with syntax like this than I do--so I let it do the heavy lifting. ps. You may want to look at =match() to see if that does what you need. =match(a4,'[book 8.xls]Sheet1'!$A$4:$a$1000,0) kind of like: =if(isnumber(match(a4,'[book 8.xls]Sheet1'!$A$4:$a$1000,0)),"a match!","Nope") I think most people use =vlookup() to return a value from an adjacent cell. Churley wrote: I am attempting to use VLOOKUP to link 2 workbooks together. This formula works fine: =VLOOKUP(A4,[test1.xls]Sheet1!$A$4:$A$1000,1,FALSE), However, when I try to copy this formula into another workbook where the workbook name has a space in it, i.e., test 1, I get this error message: Name invalid. Any Suggestions?? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup multiple workbooks
You'll need single quotes ( ' ) around the filename and sheetname when
there's a space involved in the name. So rather than just: =VLOOKUP(A4,[test1.xls]Sheet 1!$A$4:$A$1000,1,FALSE) you'll need: =VLOOKUP(A4,'[test1.xls]Sheet 1'!$A$4:$A$1000,1,FALSE) -Simon "Churley" wrote: I am attempting to use VLOOKUP to link 2 workbooks together. This formula works fine: =VLOOKUP(A4,[test1.xls]Sheet1!$A$4:$A$1000,1,FALSE), However, when I try to copy this formula into another workbook where the workbook name has a space in it, i.e., test 1, I get this error message: Name invalid. Any Suggestions?? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup multiple workbooks
"Dave Peterson" wrote: If you build your formula by typing: =vlookup(a4, then use Window (on the worksheet menu bar) to select the other workbook then go to sheet1 of that other workbook and point at the range (a4:a1000), you'll see that excel will add some apostrophes. =VLOOKUP(A4,'[test 1.xls]Sheet1'!$A$4:$a$1000,1,FALSE) I've found that excel does a much better job with syntax like this than I do--so I let it do the heavy lifting. ps. You may want to look at =match() to see if that does what you need. =match(a4,'[book 8.xls]Sheet1'!$A$4:$a$1000,0) kind of like: =if(isnumber(match(a4,'[book 8.xls]Sheet1'!$A$4:$a$1000,0)),"a match!","Nope") I think most people use =vlookup() to return a value from an adjacent cell. Churley wrote: I am attempting to use VLOOKUP to link 2 workbooks together. This formula works fine: =VLOOKUP(A4,[test1.xls]Sheet1!$A$4:$A$1000,1,FALSE), However, when I try to copy this formula into another workbook where the workbook name has a space in it, i.e., test 1, I get this error message: Name invalid. Any Suggestions?? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup multiple workbooks
"SimonCC" wrote: You'll need single quotes ( ' ) around the filename and sheetname when there's a space involved in the name. So rather than just: =VLOOKUP(A4,[test1.xls]Sheet 1!$A$4:$A$1000,1,FALSE) you'll need: =VLOOKUP(A4,'[test1.xls]Sheet 1'!$A$4:$A$1000,1,FALSE) -Simon "Churley" wrote: I am attempting to use VLOOKUP to link 2 workbooks together. This formula works fine: =VLOOKUP(A4,[test1.xls]Sheet1!$A$4:$A$1000,1,FALSE), However, when I try to copy this formula into another workbook where the workbook name has a space in it, i.e., test 1, I get this error message: Name invalid. Any Suggestions?? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup multiple workbooks
Thank you for your response.This was very helpful!
"SimonCC" wrote: You'll need single quotes ( ' ) around the filename and sheetname when there's a space involved in the name. So rather than just: =VLOOKUP(A4,[test1.xls]Sheet 1!$A$4:$A$1000,1,FALSE) you'll need: =VLOOKUP(A4,'[test1.xls]Sheet 1'!$A$4:$A$1000,1,FALSE) -Simon "Churley" wrote: I am attempting to use VLOOKUP to link 2 workbooks together. This formula works fine: =VLOOKUP(A4,[test1.xls]Sheet1!$A$4:$A$1000,1,FALSE), However, when I try to copy this formula into another workbook where the workbook name has a space in it, i.e., test 1, I get this error message: Name invalid. Any Suggestions?? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup multiple workbooks
Thank you very much. This was very helpful!!
"Dave Peterson" wrote: If you build your formula by typing: =vlookup(a4, then use Window (on the worksheet menu bar) to select the other workbook then go to sheet1 of that other workbook and point at the range (a4:a1000), you'll see that excel will add some apostrophes. =VLOOKUP(A4,'[test 1.xls]Sheet1'!$A$4:$a$1000,1,FALSE) I've found that excel does a much better job with syntax like this than I do--so I let it do the heavy lifting. ps. You may want to look at =match() to see if that does what you need. =match(a4,'[book 8.xls]Sheet1'!$A$4:$a$1000,0) kind of like: =if(isnumber(match(a4,'[book 8.xls]Sheet1'!$A$4:$a$1000,0)),"a match!","Nope") I think most people use =vlookup() to return a value from an adjacent cell. Churley wrote: I am attempting to use VLOOKUP to link 2 workbooks together. This formula works fine: =VLOOKUP(A4,[test1.xls]Sheet1!$A$4:$A$1000,1,FALSE), However, when I try to copy this formula into another workbook where the workbook name has a space in it, i.e., test 1, I get this error message: Name invalid. Any Suggestions?? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
work with multiple workbooks on separate monitor for 2003 edition | Excel Worksheet Functions | |||
VLookup across Workbooks? | Excel Worksheet Functions | |||
Vlookup on multiple workbooks | Excel Discussion (Misc queries) | |||
Vlookup in Multiple Workbooks | Excel Discussion (Misc queries) | |||
adding certain cells in multiple worksheets in multiple workbooks | Excel Worksheet Functions |