i m not getting the value of a cell which have contains path in vlookup formula
- Thread starter mahaveer
- Start date
vijay.vizzu
Member
Dear Mahaveer,
1. Please check "F:data[EMPLOYEE7.xlsx]EMPLOYEE7'!$A$2:$C$31" file open or not. If not then please open that file and recheck it.
1. Please check "F:data[EMPLOYEE7.xlsx]EMPLOYEE7'!$A$2:$C$31" file open or not. If not then please open that file and recheck it.
mahaveer
Member
hey vijay.vizzu
actually when i put b1 - =VLOOKUP(A1,F:data[EMPLOYEE7.xlsx]EMPLOYEE7'!$A$2:$C$31,2,FALSE) then i got write answer without opening the "employee7" file.
n i think there should be no effect whether "employee7" file is open or not.
so what i should do ?
Regards
CA Mahaveer Somani
actually when i put b1 - =VLOOKUP(A1,F:data[EMPLOYEE7.xlsx]EMPLOYEE7'!$A$2:$C$31,2,FALSE) then i got write answer without opening the "employee7" file.
n i think there should be no effect whether "employee7" file is open or not.
so what i should do ?
Regards
CA Mahaveer Somani
sgmpatnaik
Active Member
@Mahaveer
Hi
sorry i can't get you, can you please give some details actually what you are going to achieve
Thanks
SP
Hi
sorry i can't get you, can you please give some details actually what you are going to achieve
Thanks
SP
SirJB7
Excel Rōnin
Hi, mahaveer!
As the Excel built-in help states for the second parameter of VLOOKUP function, it refers to a matrix where to perform the search, and it adds that it may be a range of cells or a reference to a named range. In both cases that parameter should return a "range". That's from the help.
Now, when you enter the full range path as a parameter Excel evaluates it as a range, so it works. But when you enter it at F1 cell, it becomes a string, nothing more, so the formula doesn't work as it receives a string instead of a range.
Hope it helps.
Regards!
As the Excel built-in help states for the second parameter of VLOOKUP function, it refers to a matrix where to perform the search, and it adds that it may be a range of cells or a reference to a named range. In both cases that parameter should return a "range". That's from the help.
Now, when you enter the full range path as a parameter Excel evaluates it as a range, so it works. But when you enter it at F1 cell, it becomes a string, nothing more, so the formula doesn't work as it receives a string instead of a range.
Hope it helps.
Regards!
Hi Mahaveer,
I don't know but today something is wrong at my end. I am not able to download files.
But could you plz have look at the below discussion? It talks about the similar kind of problem and gives the solution as well.
http://www.ozgrid.com/forum/showthread.php?t=80024
Kaushik
I don't know but today something is wrong at my end. I am not able to download files.
But could you plz have look at the below discussion? It talks about the similar kind of problem and gives the solution as well.
http://www.ozgrid.com/forum/showthread.php?t=80024
Kaushik
NARAYANK991
Excel Ninja
Hi Mahaveer ,
The INDIRECT function will work if the worksheet and cell references are separated from the other symbols.
For example , the complete address consists of the following components :
1. The single quote symbol '
2. The complete sheet name , including the path and file name F:data[EMPLOYEE7.xlsx]EMPLOYEE7
3. The single quote symbol '
4. The exclamation mark !
5. The complete range address $A$2:$C$31
Only (2) and (5) above can be in cells ; the others have to be specified directly. If you have (2) in C1 , and (5) in D1 , then the following will work :
=VLOOKUP(A1,INDIRECT("'"&C1&"'!"&D1),2,FALSE)
Of course , this will work only when the file EMPLOYEE.xlsx is open ; when it is closed , you will get a #REF! error.
Narayan
The INDIRECT function will work if the worksheet and cell references are separated from the other symbols.
For example , the complete address consists of the following components :
1. The single quote symbol '
2. The complete sheet name , including the path and file name F:data[EMPLOYEE7.xlsx]EMPLOYEE7
3. The single quote symbol '
4. The exclamation mark !
5. The complete range address $A$2:$C$31
Only (2) and (5) above can be in cells ; the others have to be specified directly. If you have (2) in C1 , and (5) in D1 , then the following will work :
=VLOOKUP(A1,INDIRECT("'"&C1&"'!"&D1),2,FALSE)
Of course , this will work only when the file EMPLOYEE.xlsx is open ; when it is closed , you will get a #REF! error.
Narayan
mahaveer
Member
no kaushik it is not possible for me that i can use this formula becoz as narayank is telling to me us that we have to open employee7 file for getting the data
actually i have to get data from so many files therefore i cannot open all files to get data...
now is there any solution for me?
Regards
CA Mahaveer Somani
actually i have to get data from so many files therefore i cannot open all files to get data...
now is there any solution for me?
Regards
CA Mahaveer Somani
NARAYANK991
Excel Ninja
Hi Mahaveer ,
The only solution is to use VBA ; the most well-known VBA code for this is Harlan Grove's PULL function ; there are other add-ins such as Laurent Longre's MOREFUNC ; you can google for both of these ; to start with check out this link :
https://groups.google.com/forum/?fromgroups#!msg/microsoft.public.excel.worksheet.functions/l9ObQ9ku6Bk/_a2jdMD2SeIJ
Narayan
The only solution is to use VBA ; the most well-known VBA code for this is Harlan Grove's PULL function ; there are other add-ins such as Laurent Longre's MOREFUNC ; you can google for both of these ; to start with check out this link :
https://groups.google.com/forum/?fromgroups#!msg/microsoft.public.excel.worksheet.functions/l9ObQ9ku6Bk/_a2jdMD2SeIJ
Narayan