Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
Hello.
I'm using VLOOKUP to grab data from a different file... that part is pretty simple. The problem is the file name gets changed a lot. I need to have one cell that has the path, and somehow get VLOOKUP to reference it's table_array portion to that cell's path. that way instead of changing the 532 formulas everytime someone changes the file name I can just change that one cell. I'm having trouble getting this to work, I think the problem is with telling it to look at the first tab but i'm not entirely sure. please help!!!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
On Aug 13, 10:02*am, beeblemonster
wrote: Hello. I'm using VLOOKUP to grab data from a different file... that part is pretty simple. The problem is the file name gets changed a lot. I need to have one cell that has the path, and somehow get VLOOKUP to reference it's table_array portion to that cell's path. that way instead of changing the 532 formulas everytime someone changes the file name I can just change that one cell. I'm having trouble getting this to work, I think the problem is with telling it to look at the first tab but i'm not entirely sure. please help!!!!! change the table_array portion to an indirect function. If you place the filename in cell A1, then make your formula VLOOKUP(B1,INDIRECT(A1),2). Remember to place the filename in the format of something like: C:\Documents and Settings\tsides\My Documents\[Book1.xls]Sheet1'!$A $2:$B$5 You could even hardcode the path and just put the filename in A1: VLOOKUP(B1,INDIRECT("C:\Documents and Settings\tsides\My Documents\ ["&A1&"]Sheet1'!$A$2:$B$5"),2) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
The won't work since INDIRECT does not work with files that are closed so
the only way is to open the file and thus the path is not necessary just the file name. If the OP does a Google search for Morefunc he can download and install that add-in and one of the functions in that add-in is called INDIRECT.EXT which will work with closed files -- Regards, Peo Sjoblom wrote in message ... On Aug 13, 10:02 am, beeblemonster wrote: Hello. I'm using VLOOKUP to grab data from a different file... that part is pretty simple. The problem is the file name gets changed a lot. I need to have one cell that has the path, and somehow get VLOOKUP to reference it's table_array portion to that cell's path. that way instead of changing the 532 formulas everytime someone changes the file name I can just change that one cell. I'm having trouble getting this to work, I think the problem is with telling it to look at the first tab but i'm not entirely sure. please help!!!!! change the table_array portion to an indirect function. If you place the filename in cell A1, then make your formula VLOOKUP(B1,INDIRECT(A1),2). Remember to place the filename in the format of something like: C:\Documents and Settings\tsides\My Documents\[Book1.xls]Sheet1'!$A $2:$B$5 You could even hardcode the path and just put the filename in A1: VLOOKUP(B1,INDIRECT("C:\Documents and Settings\tsides\My Documents\ ["&A1&"]Sheet1'!$A$2:$B$5"),2) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
Thanks; I have many uses for that that add-in myself!
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
It's not working at all....
would it have something to do with the fact that both files aren't on my hardrive... they are on a company server in password protected folder? I've been trying to get this to work for hours... with and without it open. :( |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
Post the formula(s) that you have tried, and tell us what error
message you are getting. Pete On Aug 13, 10:00*pm, beeblemonster wrote: It's not working at all.... would it have something to do with the fact that both files aren't on my hardrive... they are on a company server in password protected folder? I've been trying to get this to work for hours... with and without it open. :( |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
I'ver tried it a couple of ways...
It says #NAME? when I dothis... This is what C3's code looks like... =VLOOKUP(A3,INDIRECT(A1),15) and this is what A1's looks like... =HYPERLINK('[file name]Sheet 1'!$1:$65536) when I change A1 to \\folder\folder\folder\[file name]Sheet 1'!$1:$65536 it come with a #REF! error "Pete_UK" wrote: Post the formula(s) that you have tried, and tell us what error message you are getting. Pete On Aug 13, 10:00 pm, beeblemonster wrote: It's not working at all.... would it have something to do with the fact that both files aren't on my hardrive... they are on a company server in password protected folder? I've been trying to get this to work for hours... with and without it open. :( |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
Try it with your table reference as $A:$P rather than $1:$65536. Not
sure why you have the HYPERLINK in A1, but you need the full path if the file is not open (although you seem to have missed an apostrophe). Hope this helps. Pete On Aug 14, 3:14*pm, beeblemonster wrote: I'ver tried it a couple of ways... It says #NAME? when I dothis... This is what C3's code looks like... =VLOOKUP(A3,INDIRECT(A1),15) and this is what A1's looks like... =HYPERLINK('[file name]Sheet 1'!$1:$65536) when I change A1 to \\folder\folder\folder\[file name]Sheet 1'!$1:$65536 it come with a #REF! error "Pete_UK" wrote: Post the formula(s) that you have tried, and tell us what error message you are getting. Pete On Aug 13, 10:00 pm, beeblemonster wrote: It's not working at all.... would it have something to do with the fact that both files aren't on my hardrive... they are on a company server in password protected folder? I've been trying to get this to work for hours... with and without it open. :(- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
Sorry, I thought from the earlier posts that you would have downloaded
the Morefunc add-in and used INDIRECT.EXT. As you are trying to use INDIRECT, this will fail if the other workbook is not open. Pete On Aug 14, 3:27*pm, Pete_UK wrote: Try it with your table reference as $A:$P rather than $1:$65536. Not sure why you have the HYPERLINK in A1, but you need the full path if the file is not open (although you seem to have missed an apostrophe). Hope this helps. Pete On Aug 14, 3:14*pm, beeblemonster wrote: I'ver tried it a couple of ways... It says #NAME? when I dothis... This is what C3's code looks like... =VLOOKUP(A3,INDIRECT(A1),15) and this is what A1's looks like... =HYPERLINK('[file name]Sheet 1'!$1:$65536) when I change A1 to \\folder\folder\folder\[file name]Sheet 1'!$1:$65536 it come with a #REF! error "Pete_UK" wrote: Post the formula(s) that you have tried, and tell us what error message you are getting. Pete On Aug 13, 10:00 pm, beeblemonster wrote: It's not working at all.... would it have something to do with the fact that both files aren't on my hardrive... they are on a company server in password protected folder? I've been trying to get this to work for hours... with and without it open. :(- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
It did the hyperlink automatically...
i can change it to just text... I'll try the A-P thing, but where am I missing an apostrophe? and I can't download morefunc, unless I get permission from an admin, so I was going to make sure I could get it to work with the file in the backround before I went through taht whole process. If I did use this program and someone wanted to look at the file and change stuff on a different computer... would they have to download the program also? "Pete_UK" wrote: Sorry, I thought from the earlier posts that you would have downloaded the Morefunc add-in and used INDIRECT.EXT. As you are trying to use INDIRECT, this will fail if the other workbook is not open. Pete On Aug 14, 3:27 pm, Pete_UK wrote: Try it with your table reference as $A:$P rather than $1:$65536. Not sure why you have the HYPERLINK in A1, but you need the full path if the file is not open (although you seem to have missed an apostrophe). Hope this helps. Pete On Aug 14, 3:14 pm, beeblemonster wrote: I'ver tried it a couple of ways... It says #NAME? when I dothis... This is what C3's code looks like... =VLOOKUP(A3,INDIRECT(A1),15) and this is what A1's looks like... =HYPERLINK('[file name]Sheet 1'!$1:$65536) when I change A1 to \\folder\folder\folder\[file name]Sheet 1'!$1:$65536 it come with a #REF! error "Pete_UK" wrote: Post the formula(s) that you have tried, and tell us what error message you are getting. Pete On Aug 13, 10:00 pm, beeblemonster wrote: It's not working at all.... would it have something to do with the fact that both files aren't on my hardrive... they are on a company server in password protected folder? I've been trying to get this to work for hours... with and without it open. :(- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
and it's on a microsoft windows network... so i can't start with C:\\ it just
starts with \\folder\ "Pete_UK" wrote: Sorry, I thought from the earlier posts that you would have downloaded the Morefunc add-in and used INDIRECT.EXT. As you are trying to use INDIRECT, this will fail if the other workbook is not open. Pete On Aug 14, 3:27 pm, Pete_UK wrote: Try it with your table reference as $A:$P rather than $1:$65536. Not sure why you have the HYPERLINK in A1, but you need the full path if the file is not open (although you seem to have missed an apostrophe). Hope this helps. Pete On Aug 14, 3:14 pm, beeblemonster wrote: I'ver tried it a couple of ways... It says #NAME? when I dothis... This is what C3's code looks like... =VLOOKUP(A3,INDIRECT(A1),15) and this is what A1's looks like... =HYPERLINK('[file name]Sheet 1'!$1:$65536) when I change A1 to \\folder\folder\folder\[file name]Sheet 1'!$1:$65536 it come with a #REF! error "Pete_UK" wrote: Post the formula(s) that you have tried, and tell us what error message you are getting. Pete On Aug 13, 10:00 pm, beeblemonster wrote: It's not working at all.... would it have something to do with the fact that both files aren't on my hardrive... they are on a company server in password protected folder? I've been trying to get this to work for hours... with and without it open. :(- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
To answer your last question first - Yes, but it's free (but that's
one reason why I don't install add-ins, as I can't guarantee that other users will have them). Open the file that you want to get the data from, and then put this, including both apostophes, in A1 of your main file: '[file_name.xls]Sheet 1'!$A:$P (You don't need to specify the path if the file is open). Then this formula in C3: =VLOOKUP(A3,INDIRECT(A$1),15) should work ok. However, if you close the other file this will not work. Hope this helps. Pete On Aug 14, 3:50*pm, beeblemonster wrote: It did the hyperlink automatically... i can change it to just text... I'll try the A-P thing, but where am I missing an apostrophe? and I can't download morefunc, unless I get permission from an admin, so I was going to make sure I could get it to work with the file in the backround before I went through taht whole process. If I did use this program and someone wanted to look at the file and change stuff on a different computer... would they have to download the program also? "Pete_UK" wrote: Sorry, I thought from the earlier posts that you would have downloaded the Morefunc add-in and used INDIRECT.EXT. As you are trying to use INDIRECT, this will fail if the other workbook is not open. Pete On Aug 14, 3:27 pm, Pete_UK wrote: Try it with your table reference as $A:$P rather than $1:$65536. Not sure why you have the HYPERLINK in A1, but you need the full path if the file is not open (although you seem to have missed an apostrophe).. Hope this helps. Pete On Aug 14, 3:14 pm, beeblemonster wrote: I'ver tried it a couple of ways... It says #NAME? when I dothis... This is what C3's code looks like... =VLOOKUP(A3,INDIRECT(A1),15) and this is what A1's looks like... =HYPERLINK('[file name]Sheet 1'!$1:$65536) when I change A1 to \\folder\folder\folder\[file name]Sheet 1'!$1:$65536 it come with a #REF! error "Pete_UK" wrote: Post the formula(s) that you have tried, and tell us what error message you are getting. Pete On Aug 13, 10:00 pm, beeblemonster wrote: It's not working at all.... would it have something to do with the fact that both files aren't on my hardrive... they are on a company server in password protected folder? I've been trying to get this to work for hours... with and without it open. :(- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
It's still not working.
The file being called upon is open behind it. I have '[file name.xls]sheet 1'!$A:$X in A1. and C3 is showing... #REF! with =VLOOKUP(A3,INDIRECT(A$1),15) as the code. I can't find the mistake anywhere... "Pete_UK" wrote: To answer your last question first - Yes, but it's free (but that's one reason why I don't install add-ins, as I can't guarantee that other users will have them). Open the file that you want to get the data from, and then put this, including both apostophes, in A1 of your main file: '[file_name.xls]Sheet 1'!$A:$P (You don't need to specify the path if the file is open). Then this formula in C3: =VLOOKUP(A3,INDIRECT(A$1),15) should work ok. However, if you close the other file this will not work. Hope this helps. Pete On Aug 14, 3:50 pm, beeblemonster wrote: It did the hyperlink automatically... i can change it to just text... I'll try the A-P thing, but where am I missing an apostrophe? and I can't download morefunc, unless I get permission from an admin, so I was going to make sure I could get it to work with the file in the backround before I went through taht whole process. If I did use this program and someone wanted to look at the file and change stuff on a different computer... would they have to download the program also? "Pete_UK" wrote: Sorry, I thought from the earlier posts that you would have downloaded the Morefunc add-in and used INDIRECT.EXT. As you are trying to use INDIRECT, this will fail if the other workbook is not open. Pete On Aug 14, 3:27 pm, Pete_UK wrote: Try it with your table reference as $A:$P rather than $1:$65536. Not sure why you have the HYPERLINK in A1, but you need the full path if the file is not open (although you seem to have missed an apostrophe).. Hope this helps. Pete On Aug 14, 3:14 pm, beeblemonster wrote: I'ver tried it a couple of ways... It says #NAME? when I dothis... This is what C3's code looks like... =VLOOKUP(A3,INDIRECT(A1),15) and this is what A1's looks like... =HYPERLINK('[file name]Sheet 1'!$1:$65536) when I change A1 to \\folder\folder\folder\[file name]Sheet 1'!$1:$65536 it come with a #REF! error "Pete_UK" wrote: Post the formula(s) that you have tried, and tell us what error message you are getting. Pete On Aug 13, 10:00 pm, beeblemonster wrote: It's not working at all.... would it have something to do with the fact that both files aren't on my hardrive... they are on a company server in password protected folder? I've been trying to get this to work for hours... with and without it open. :(- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
You can actually embed the function in the particular workbook so if you
email a workbook the receiver don't have to download and install the add-in. -- Regards, Peo Sjoblom "Pete_UK" wrote in message ... To answer your last question first - Yes, but it's free (but that's one reason why I don't install add-ins, as I can't guarantee that other users will have them). Open the file that you want to get the data from, and then put this, including both apostophes, in A1 of your main file: '[file_name.xls]Sheet 1'!$A:$P (You don't need to specify the path if the file is open). Then this formula in C3: =VLOOKUP(A3,INDIRECT(A$1),15) should work ok. However, if you close the other file this will not work. Hope this helps. Pete On Aug 14, 3:50 pm, beeblemonster wrote: It did the hyperlink automatically... i can change it to just text... I'll try the A-P thing, but where am I missing an apostrophe? and I can't download morefunc, unless I get permission from an admin, so I was going to make sure I could get it to work with the file in the backround before I went through taht whole process. If I did use this program and someone wanted to look at the file and change stuff on a different computer... would they have to download the program also? "Pete_UK" wrote: Sorry, I thought from the earlier posts that you would have downloaded the Morefunc add-in and used INDIRECT.EXT. As you are trying to use INDIRECT, this will fail if the other workbook is not open. Pete On Aug 14, 3:27 pm, Pete_UK wrote: Try it with your table reference as $A:$P rather than $1:$65536. Not sure why you have the HYPERLINK in A1, but you need the full path if the file is not open (although you seem to have missed an apostrophe). Hope this helps. Pete On Aug 14, 3:14 pm, beeblemonster wrote: I'ver tried it a couple of ways... It says #NAME? when I dothis... This is what C3's code looks like... =VLOOKUP(A3,INDIRECT(A1),15) and this is what A1's looks like... =HYPERLINK('[file name]Sheet 1'!$1:$65536) when I change A1 to \\folder\folder\folder\[file name]Sheet 1'!$1:$65536 it come with a #REF! error "Pete_UK" wrote: Post the formula(s) that you have tried, and tell us what error message you are getting. Pete On Aug 13, 10:00 pm, beeblemonster wrote: It's not working at all.... would it have something to do with the fact that both files aren't on my hardrive... they are on a company server in password protected folder? I've been trying to get this to work for hours... with and without it open. :(- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
beeblemonster wrote:
It's still not working. The file being called upon is open behind it. I have '[file name.xls]sheet 1'!$A:$X in A1. and C3 is showing... #REF! with =VLOOKUP(A3,INDIRECT(A$1),15) as the code. I can't find the mistake anywhere... Does A1 actually say "file name" and "sheet 1", or did you substitute the file name and sheet name you actually are trying to reference. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
no. File name and Sheet 1 are replacements of what I have. I can't really
send that information out. But sheet 1 is replaced by the name of my first tab and file name is exactly as the file name is. [bla bla bla (Updated aug08).xls] "Glenn" wrote: beeblemonster wrote: It's still not working. The file being called upon is open behind it. I have '[file name.xls]sheet 1'!$A:$X in A1. and C3 is showing... #REF! with =VLOOKUP(A3,INDIRECT(A$1),15) as the code. I can't find the mistake anywhere... Does A1 actually say "file name" and "sheet 1", or did you substitute the file name and sheet name you actually are trying to reference. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
how?
"Peo Sjoblom" wrote: You can actually embed the function in the particular workbook so if you email a workbook the receiver don't have to download and install the add-in. -- Regards, Peo Sjoblom "Pete_UK" wrote in message ... To answer your last question first - Yes, but it's free (but that's one reason why I don't install add-ins, as I can't guarantee that other users will have them). Open the file that you want to get the data from, and then put this, including both apostophes, in A1 of your main file: '[file_name.xls]Sheet 1'!$A:$P (You don't need to specify the path if the file is open). Then this formula in C3: =VLOOKUP(A3,INDIRECT(A$1),15) should work ok. However, if you close the other file this will not work. Hope this helps. Pete On Aug 14, 3:50 pm, beeblemonster wrote: It did the hyperlink automatically... i can change it to just text... I'll try the A-P thing, but where am I missing an apostrophe? and I can't download morefunc, unless I get permission from an admin, so I was going to make sure I could get it to work with the file in the backround before I went through taht whole process. If I did use this program and someone wanted to look at the file and change stuff on a different computer... would they have to download the program also? "Pete_UK" wrote: Sorry, I thought from the earlier posts that you would have downloaded the Morefunc add-in and used INDIRECT.EXT. As you are trying to use INDIRECT, this will fail if the other workbook is not open. Pete On Aug 14, 3:27 pm, Pete_UK wrote: Try it with your table reference as $A:$P rather than $1:$65536. Not sure why you have the HYPERLINK in A1, but you need the full path if the file is not open (although you seem to have missed an apostrophe). Hope this helps. Pete On Aug 14, 3:14 pm, beeblemonster wrote: I'ver tried it a couple of ways... It says #NAME? when I dothis... This is what C3's code looks like... =VLOOKUP(A3,INDIRECT(A1),15) and this is what A1's looks like... =HYPERLINK('[file name]Sheet 1'!$1:$65536) when I change A1 to \\folder\folder\folder\[file name]Sheet 1'!$1:$65536 it come with a #REF! error "Pete_UK" wrote: Post the formula(s) that you have tried, and tell us what error message you are getting. Pete On Aug 13, 10:00 pm, beeblemonster wrote: It's not working at all.... would it have something to do with the fact that both files aren't on my hardrive... they are on a company server in password protected folder? I've been trying to get this to work for hours... with and without it open. :(- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
morefunc is a series of UDFs. User Defined Function. (I think)
Basically, UDFs are macros that create functions you can use like any other function. Once you install it, it offers the option right in the Tools menu to embed its macros into any file you choose. |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
What do you have in A3? Does it actually match with something in
column A of the external file? The way your VLOOKUP formula is set up, the data in the external file needs to be sorted on column A, and if A3 is empty or smaller than the first value then you will get an error. You might like to look for an exact match, by changing your formula to: =VLOOKUP(A3,INDIRECT(A$1),15,0) and then the data doesn't have to be sorted. Hope this helps. Pete On Aug 14, 6:26*pm, beeblemonster wrote: no. File name and Sheet 1 are replacements of what I have. I can't really send that information out. But sheet 1 is replaced by the name of my first tab and file name is exactly as the file name is. [bla bla bla (Updated aug08).xls] "Glenn" wrote: beeblemonster wrote: It's still not working. The file being called upon is open behind it. I have '[file name.xls]sheet 1'!$A:$X in A1. and C3 is showing... #REF! with =VLOOKUP(A3,INDIRECT(A$1),15) as the code. I can't find the mistake anywhere... Does A1 actually say "file name" and "sheet 1", or did you substitute the file name and sheet name you actually are trying to reference.- Hide quoted text - - Show quoted text - |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
there is definately a match there because i can do the entire formula within
A3 and it works. something between cells A1 and C3 is not working. #REF! still comes up when I add a 0 or false to the end. "Pete_UK" wrote: What do you have in A3? Does it actually match with something in column A of the external file? The way your VLOOKUP formula is set up, the data in the external file needs to be sorted on column A, and if A3 is empty or smaller than the first value then you will get an error. You might like to look for an exact match, by changing your formula to: =VLOOKUP(A3,INDIRECT(A$1),15,0) and then the data doesn't have to be sorted. Hope this helps. Pete On Aug 14, 6:26 pm, beeblemonster wrote: no. File name and Sheet 1 are replacements of what I have. I can't really send that information out. But sheet 1 is replaced by the name of my first tab and file name is exactly as the file name is. [bla bla bla (Updated aug08).xls] "Glenn" wrote: beeblemonster wrote: It's still not working. The file being called upon is open behind it. I have '[file name.xls]sheet 1'!$A:$X in A1. and C3 is showing... #REF! with =VLOOKUP(A3,INDIRECT(A$1),15) as the code. I can't find the mistake anywhere... Does A1 actually say "file name" and "sheet 1", or did you substitute the file name and sheet name you actually are trying to reference.- Hide quoted text - - Show quoted text - |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
OK. I made a brand new file because i think the last one was corrupted.
meh. If I put... =VLOOKUP(A4,'[file name (Updated 1august 2008).xls]Sheet 1'!$A:$Y,2) in cell B4, then it works If I put... =VLOOKUP(A6'\\folder\folder\folder\folder\folder\[file name (Updated 1august 2008).xls]Sheet 1'!$A:$Y,2) in cell B6, then it works BUT IF I PUT... '[file name (Updated 1august 2008).xls]Sheet 1'!$A:$Y,2) in A1, and =VLOOKUP(A4,INDIRECT(E16),2,0) or =VLOOKUP(A4,INDIRECT.EXT(E16),2,0) in B5... it gives back #REF! WHAT AM I DOING WRONG????!!! "beeblemonster" wrote: there is definately a match there because i can do the entire formula within A3 and it works. something between cells A1 and C3 is not working. #REF! still comes up when I add a 0 or false to the end. "Pete_UK" wrote: What do you have in A3? Does it actually match with something in column A of the external file? The way your VLOOKUP formula is set up, the data in the external file needs to be sorted on column A, and if A3 is empty or smaller than the first value then you will get an error. You might like to look for an exact match, by changing your formula to: =VLOOKUP(A3,INDIRECT(A$1),15,0) and then the data doesn't have to be sorted. Hope this helps. Pete On Aug 14, 6:26 pm, beeblemonster wrote: no. File name and Sheet 1 are replacements of what I have. I can't really send that information out. But sheet 1 is replaced by the name of my first tab and file name is exactly as the file name is. [bla bla bla (Updated aug08).xls] "Glenn" wrote: beeblemonster wrote: It's still not working. The file being called upon is open behind it. I have '[file name.xls]sheet 1'!$A:$X in A1. and C3 is showing... #REF! with =VLOOKUP(A3,INDIRECT(A$1),15) as the code. I can't find the mistake anywhere... Does A1 actually say "file name" and "sheet 1", or did you substitute the file name and sheet name you actually are trying to reference.- Hide quoted text - - Show quoted text - |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using VLOOKUP with a reference to a different file...
You can't have the apostrophes in the cell nor the range, you can use it
like this =VLOOKUP(A2,INDIRECT("'"&A1&"'!$A:$Y"),2,0) Where you would have the open filename and the worksheet in A1 and the first apostrophe concatenated with the ampersand before and after A1 then a text representation of this !$A:$Y That works for me, note that indirect formula will never adapt to when you close the file since it is a text string -- Regards, Peo Sjoblom "beeblemonster" wrote in message ... OK. I made a brand new file because i think the last one was corrupted. meh. If I put... =VLOOKUP(A4,'[file name (Updated 1august 2008).xls]Sheet 1'!$A:$Y,2) in cell B4, then it works If I put... =VLOOKUP(A6'\\folder\folder\folder\folder\folder\[file name (Updated 1august 2008).xls]Sheet 1'!$A:$Y,2) in cell B6, then it works BUT IF I PUT... '[file name (Updated 1august 2008).xls]Sheet 1'!$A:$Y,2) in A1, and =VLOOKUP(A4,INDIRECT(E16),2,0) or =VLOOKUP(A4,INDIRECT.EXT(E16),2,0) in B5... it gives back #REF! WHAT AM I DOING WRONG????!!! "beeblemonster" wrote: there is definately a match there because i can do the entire formula within A3 and it works. something between cells A1 and C3 is not working. #REF! still comes up when I add a 0 or false to the end. "Pete_UK" wrote: What do you have in A3? Does it actually match with something in column A of the external file? The way your VLOOKUP formula is set up, the data in the external file needs to be sorted on column A, and if A3 is empty or smaller than the first value then you will get an error. You might like to look for an exact match, by changing your formula to: =VLOOKUP(A3,INDIRECT(A$1),15,0) and then the data doesn't have to be sorted. Hope this helps. Pete On Aug 14, 6:26 pm, beeblemonster wrote: no. File name and Sheet 1 are replacements of what I have. I can't really send that information out. But sheet 1 is replaced by the name of my first tab and file name is exactly as the file name is. [bla bla bla (Updated aug08).xls] "Glenn" wrote: beeblemonster wrote: It's still not working. The file being called upon is open behind it. I have '[file name.xls]sheet 1'!$A:$X in A1. and C3 is showing... #REF! with =VLOOKUP(A3,INDIRECT(A$1),15) as the code. I can't find the mistake anywhere... Does A1 actually say "file name" and "sheet 1", or did you substitute the file name and sheet name you actually are trying to reference.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking to another file using file reference typed cell | Excel Worksheet Functions | |||
cross-reference data from one file to another file | Excel Discussion (Misc queries) | |||
VLOOKUP Reference Help | Excel Discussion (Misc queries) | |||
How do I reference external data from a file, file name found in . | Excel Discussion (Misc queries) | |||
copy/paste from one file to another without file name reference | Excel Discussion (Misc queries) |