Excel vlookup formula references another workbook and the result won't show

[dekkerge]

[FONT=&quot]I have a long list of formulas which use a vlookup to other workbooks. I wanted to change the date in the name of the workbook being refereced in the formulas so I tried that by the find and replace function but when I do that, an "Update Vales" window pops up and when I close that window, only one of the formulas replaces the value. [/FONT]
[FONT=&quot]I got around this by changing the "=" to "#" then replacing the date and changing the "#" back to "=" but now the formula shows in the cell and not the result. I can click into an individual cell and then press enter and get the "Update Values" window to pop up, close it, and get the result to show, but I have thousands of the formula and it is not efficient or fun to go through each cell individually and do that. is there an easier way to do this?[/FONT]
[FONT=&quot]Ex: [/FONT]
[FONT=&quot]=IFNA(VLOOKUP($C21,'S:\FINSC\[Report 2018 Roll 1.xlsx]By Department'!$J$5:$M$26,2,FALSE),0)[/FONT]
[FONT=&quot]This is the current formula and I would like to change the "2018" to "2019"[/FONT]
[FONT=&quot]The new workbooks do not exist yet but I want to set up the file so that as soon as I create the new workbooks each week, they will pull into this file. I was able to do this for the last few months of 2018 but that was a much smaller amount of cells. I can't seem to do get them to change to the result quickly now. Are there any suggestions for this?[/FONT]

 

[BGY23]

You can try this.

1 Open the file with your vlookups in and save as and create the file for 2019. keep it open while you do the next bit.
2 Now open the file(s) which the vlookup references and save as each file for 2019. It doesn't mattere its got 2018 data in.
3 The file in 1 above will change the formulas automaticaly

When you've finished close the workbooks and now prepare the files in 2 above for 2019.

Good luck

 

[BGY23]

Option 2 is create your refernce workbooks for 2019 Go into your summary workbook and do File> edit links and change the data source(s)