Date changes everyday on referenced file. Is there a way to make the formula find the new date?

[fmoaveni]

Hi everyone,
I feel like this should not be so hard to figure out, but I am using excel 2013 and have this spreadsheet that is almost finished. The last part I need is referencing another file that changes dates everyday.

018_ArtikelInfo_20151215.csv

That is an example of what the file is called. Then the next day it would show

018_ArtikelInfo_20151216.csv

next day

018_ArtikelInfo_20151217.csv

and so on...

I have a cell on sheet3 of my workbook...well I guess it is an entire column...that has a formula to reference this workbook. It is a vlookup and it uses the information in column c to find the information on the 018_ArtikelInfo_20151215.csv excel file and pull in the name of the product that the cell in column c references in the excel workbook I am making.

The 018_ArtikelInfo_20151215.csv sheet has the info from column c on my workbook in column a. In column D on the 018_ArtikelInfo_20151215.csv workbook is where I need to pull the information from.

I can pull the info without a problem, but then the next day the formula doesn't get the newest information from the sheet with the new date.

Is there a formula or a macro that would solve this?

Unfortunately I can't post a copy of the sheet I am referencing because of Data Protection Laws.

Please let me know if there is any information you need to help! I really appreciate the help on this amazing site so far!

 

[Andrew Poulsom]

You can't use VLOOKUP on a csv file unless it's really an Excel file with a csv extension.

 

[fmoaveni]

=VLOOKUP(C2;'[018_ArtikelInfo_20151216.csv]018_ArtikelInfo_20151216'!$1:$1048576;4;0)

So not only is the excel workbook named with a date, but the tab within the workbook as well. This is what it looked like when creating the formula, but after pressing enter, it looked like this

=VLOOKUP([@[Artikel Nummer]];'018_ArtikelInfo_20151216.csv'!$1:$1048576;4;0)

I created a cell that turned today's date into text and formated it to match the date at the end of the workbook saved file.

How do I go around creating an INDIRECT formula? I have tried already and looked at a lot of websites I just cannot seem to get it right.

The cell I am wanting to reference which holds the correct date format after being converted is in cell W12.
The formulas I pasted earlier on this post are the vlookups I have tried but I don't know where to enter the INDIRECT function into it to reference the ArtikelInfo workbook. Thank you for anymore help you can give!

 

[fmoaveni]

Hi Andrew. I know the vlookup works because I tried it and the corresponding names showed up next to the referenced numbers. It says it is a .csv but when I open the file, it automatically opens in excel. Is there something I am not getting here? I am sorry to seem helpless but I am trying to learn along the way. Thank you!

 

[Andrew Poulsom]

If you open the csv, as you have done because your formula doesn't include the path, it becomes an Excel workbook with a single worksheet. If it's open you should be able to use INDIRECT like this:

=VLOOKUP([@[Artikel Nummer]];INDIRECT("'018_ArtikelInfo_"&A1&".csv'!$1:$1048576");4;0)

where A1 contains eg 20151216.