Variable latest file name referencing with formula or vba code

[Jsallen]

I am currently making a dashboard that will be drawing data from a variety of different workbooks through the company.


For a particular section, I need to reference reports for the current month, which are saved in a "XXXXX-October.xlsx" format. Since multiple people work on these, it's not really feasible to try to have them all remember to save a "dashboard reference" copy of the file every month as it is updated.


Is there a way that I can use VLOOKUP, MATCH, etc. on this file and have a variable in the formula that can automatically look for the current month's file version?


Thanks.

 

[Faseeh]

Hi Jsallen,


Welcome to the forums!!


If you just want to perform VLOOKUP() i.e. lookup a value and then fetch the corresponding value to the dashboard you can use:

=VLOOKUP("A",INDIRECT("'["&D1&"]Sheet1'!A2:C4"),2,TRUE)

where D1 contains the name of the file and your lookup table is present on its first sheet in the range A2:C4, and you are looking for "A" in.


The file that you want to lookinto must be opened.


You might be interested into vlookup() with closed workbooks, see here


Regards,

Faseeh

 

[Jsallen]

Although that IS useful to me, it doesn't really answer my original question.


What I'm trying to do, if possible, is this;


Say I have a file names "DATA-SEPTEMBER.xlsx"


and


" =VLOOKUP(A16,'C:Documents and SettingsfolderDATA-SEPTEMBER.xlsx'!$A$7:$BF$32,58,FALSE)"


Next month, that same cell needs to be updated from "DATA-OCTOBER.xlsx" instead.


Is there a way to replace 'C:Documents and SettingsfolderDATA-SEPTEMBER.xlsx' with some expression that can get the most recent DATA-*.xlsx filename?

 

[Faseeh]

Jsllen,


So far as is in my knowledge, you need to enter the name of the file, should that be the latest one will solve the issue. I think you should ask for some vba comes to resolve the issue.


Can i change the title of your thread to "Variable & latest file name referencing with formula or vba code"??


Regards,

 

[Jsallen]

Sure, much appreciated. I had a feeling any solution would involve some code (which I'm terrible at).