Hello excel experts,
I am working on an excel sheet that does some price calculation for me. I am using Vlookup function to automatically read the required inputs for my calculations from a few other excel files. This is going well for most files, except for one that I need to download from the web, and the name of this file is changing every week based on the week number and year; The name of the tabs in that excel file however remain unchanged.
The name of the excel file that I need to read from is: "Input 2016_WeekX.xlsb", wherein X (and also year number) is updated every week. The name of the tabs that I need to look into remain as "Product of interest". Because of the change on file's name, when I download the file into my calculations folder, my calculation sheet cannot be automatically updated, and I have to relink manually the required cells to the new excel file every week.
I thought that I could fix this by writing a formula like:
VLOOKUP("Myproduct";'["Input "&YEAR(TODAY())&"_Week"&WEEKNUM(TODAY())&".xlsb"]Product of interest'!$C$260:$S$456; MATCH("Mylocation";'["Input "&YEAR(TODAY())&"_Week"&WEEKNUM(TODAY())&".xlsb"]Product of interest'!$C$260:$S$260;0); FALSE).
However this does not work. I was wondering if there is a smart way for doing this to avoid manually linking every week the required cells? I appreciate it if someone can help me out. Thank you.
Bookmarks