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!

 

[AliGW]

I suspect that you will need to use the INDIRECT function for the date part of the file name, and then have a cell somewhere in your workbook that displays today's date in that format, and use this as your INDIRECT reference.

 

[Sixthsense]

Indirect() won't work with the closed workbook.

Use Indirect.Ext() of MoreFunc addin

 

[fmoaveni]

Thank you for the reply, but is it possible for you to give an example formula? I am not quite sure how those work.

 

[Sixthsense]

Check the below link:-

INDIRECT.EXT | Ashish Mathur's Blog

 

[fmoaveni]

Thank you for the blog link. Now the problem is that I cannot install add-ins on this computer without an administrator password. Is there a work around that anyone knows of? Maybe a macro I could copy in or something? Thanks again!

 

[Andrew Poulsom]

Can't you just Edit Links and change the Source?

 

[fmoaveni]

Hi Andrew,
Do you mean edit the Links in my excel everyday to match the name of the new file? Could you maybe elaborate a bit? I am open to trying anything but I just am not sure what you meant by that. Thank you!

 

[Andrew Poulsom]

Click Edit Links in the Connections Group on the Ribbon's Data tab. There you can change the source of your links.

 

[fmoaveni]

Ok I just tried that and when I go to edit links and search for the new file, it can't seem to locate it since it is a .csv file and not a normal .xls file. Any further suggestions?

the file name is called
018_ArtikelInfo_20151215.csv

The formula I have currently in the cells are
=IFERROR(VLOOKUP([@[Artikel Nummer]];'018_ArtikelInfo_20151216.csv'!$1:$1048576;4;0);"")

and I know there are semi colons and weird spelling, I am in Germany and the excel here requires the semi colon instead of a comma for some reason.