VLOOKUP according to location not NAME

Solved/Closed
carman - Aug 5, 2010 at 01:23 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
- Aug 9, 2010 at 10:36 AM
Hello,
i put the name of the files i want to perform in some cells and wrote a maco for excel to call up the file according to the location. (lets say I write 1.xls in cells Cells(2, "B"))
e.g.
A = Cells(1, "B")
B = Cells(2, "B")
C = Cells(3, "B")
Workbooks.Open Filename:=A
Windows(A).Activate

Now, i want to do VLOOKUP by looking up "B" (which is cells(2,"B") i.e. 1.xls)

from the macro i record, it becomes :

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[1.xls!R4C1:R6733C2,2,FALSE)"

However, since i will change the name of the file i want to lookup in ther future, i do NOT want the phase "1.xls" to appear in the formula... but only the location (i.e B) for excel to look up.

Please help. Thanks!!

2 replies

To be more specific, how can i change the code so that excel can look up the data i want according to the file names( that i would change in the future) i put in the specific cells' location (e.g. cells (2, "B")) ,but not the "absolute" filename? Thanks.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Aug 5, 2010 at 07:26 AM
and how would the file name change ?
Right now you are using a lookup that looks up the value in some other book. You always have to code for that workbook name, else you would be looking in the same workbook
workbook name -- sheet name -- cell address is how the lookup works

if workbook name is skipped, active workbook is assumed
if sheet name is skipped, then active sheet is assumed
0
Thanks for your reply but that wasnt my point...
i mean Could excel recognize the address of the cell that i typed in the file name instead of recognizing a specific file name?say,if i put a new file name in one cell, can excel still be able to open that new file (according to the cell's location) and do the lookup for me based on the same criteria in VLOOKUP?
what i want is, when i set A = Cells(1, "B") as my code and place 1.xls in that cell, could the VLOOKUP in VBA become

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[B!R4C1:R6733C2,2,FALSE)"

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[1.xls!R4C1:R6733C2,2,FALSE)"

So there exist no "absolute' file name but only the cell address when the filename is placed?

I need the VBA code for this because I got several worksheets for me to lookup and they all have exactly the same format ( so all the criteria for VLOOKUP is the same, except the name). Therefore i need excel to perform the lookup for me according to the new name i typed in the same cell ( B) but not asking me to select the critera again..what can i do?

please help.Thanks
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Aug 5, 2010 at 10:15 AM
Since you are doing in VBA, you can add the file name as a variable

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[" & range("A1")& "]Sheet1!R4C1:R6733C2,2,FALSE)"
0
im sorry but i don't understand how to add the file name as a variable. The file name within Cells(2, "B") changes every time when i need to update with new workbook. it could be 1.xls/abc.xls/2010-4-3.xls... etc but they would always be in the same format. in that case, i will not have a specfic file name but only a cell address where excel should do the lookup. Could u explain more details on how to make excel recognize the address?
0
The cell with the filename written on is cell(2,"B"). Thanks
0
do i need indirect function for that?
i tried =VLOOKUP(G2,INDIRECT("B2!R4C1:R6733C2"),2,FALSE) where R4C1:R6733C2 is the range i want to look up in the file i paste in B2. However, the above code doesnt work even manually using the function.. please helpp!!
0