Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 79
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
work with multiple workbooks on separate monitor for 2003 edition mathura Excel Worksheet Functions 1 July 18th 06 05:58 PM
VLookup across Workbooks? PH NEWS Excel Worksheet Functions 0 April 24th 06 03:43 PM
Vlookup on multiple workbooks clayton Excel Discussion (Misc queries) 12 January 17th 06 11:07 AM
Vlookup in Multiple Workbooks Excelcrazy Excel Discussion (Misc queries) 3 December 20th 05 02:56 PM
adding certain cells in multiple worksheets in multiple workbooks Stephen via OfficeKB.com Excel Worksheet Functions 1 February 4th 05 09:31 PM


All times are GMT +1. The time now is 07:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"