VLOOKUP according to location not NAME
Solved/Closed
carman
-
Aug 5, 2010 at 01:23 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 9, 2010 at 10:36 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 9, 2010 at 10:36 AM
Related:
- VLOOKUP according to location not NAME
- Postal address of my location - Guide
- Youtube location - Guide
- The system could not identify your location viber - Guide
- How to change download location opera gx - Guide
- Elden ring save location - Guide
2 responses
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.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Aug 5, 2010 at 07:26 AM
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
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
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Aug 5, 2010 at 10:15 AM
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)"
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'[" & range("A1")& "]Sheet1!R4C1:R6733C2,2,FALSE)"
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?
