Best way to update file link daily

[rnamhee]

I have a Master Sheet that is doing a VLOOKUP and pulling information from another sheet that I plan to download daily. The downloaded sheet is something that I will save daily in the same location and save as "VLOOKUP(.....filenameMMDDYY.xlsx...)". Every day I plan to update the formulas so that it references the most current sheet and I'm looking for the best way to go about updating it. Ideally, since the name of the referencing sheet will be the current date, I'd like to have somewhere on my master sheet that I can update the date on and have the information flow through.

Please let me know if you need more clarification:

A1: =VLOOKUP(.....cell B1 from filenameMMDDYY.xlsx...)
A2: =VLOOKUP(.....cell B2 from filenameMMDDYY.xlsx...)
A3: =VLOOKUP(.....cell B3 from filenameMMDDYY.xlsx...)
......and so on for about 5 cells down

Master Sheet: MMDDYY = Cell D1 on master sheet

If there is a better easier way to do this I'd be open to ideas.

 

[DRSteele]

Welcome to the forum.

It seems to me function INDIRECT is what you need. Both files must be open or you'll get a #REF error in the master file until you open the Date file again. And the file named for the Date file must have the data in Sheet1 (notice no spaces).

I created a file and saved it in the same folder as the Master file. I named the file 07112015.xlsx
In the master file in cell D2 I put '07112015 (which must include an apostrophe at the start because of the leading zero).
In the master file I did this:

Excel 2012

	A	B	C	D	E
b1
b2
b3
b4
b5
b6
b7

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #E2EFDA, align: right"]9/2/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]07112015[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #E2EFDA, align: right"]9/3/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #E2EFDA, align: right"]9/4/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #E2EFDA, align: right"]9/5/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #E2EFDA, align: right"]9/6/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #E2EFDA, align: right"]9/7/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #E2EFDA, align: right"]9/8/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Master​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A1[/TH]
[TD="align: left"]=INDIRECT("'["&$D$1&".xlsx]Sheet1'!"&E1)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=INDIRECT("'["&$D$1&".xlsx]Sheet1'!"&E2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]=INDIRECT("'["&$D$1&".xlsx]Sheet1'!"&E3)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A4[/TH]
[TD="align: left"]=INDIRECT("'["&$D$1&".xlsx]Sheet1'!"&E4)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]=INDIRECT("'["&$D$1&".xlsx]Sheet1'!"&E5)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A6[/TH]
[TD="align: left"]=INDIRECT("'["&$D$1&".xlsx]Sheet1'!"&E6)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A7[/TH]
[TD="align: left"]=INDIRECT("'["&$D$1&".xlsx]Sheet1'!"&E7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[rnamhee]

Thanks! Any way to get this to work without having the source files open?

 

[rnamhee]

Thanks! Any way to get this to work without having the source files open?

I'm open to other methods of getting this done. The main goal is to have links update from a new sheet daily but I'd like an easy way to facilitate updating the file name (link location)