Update source location of linked data using string in formula & values worksheet

[john316swan]

First thread, please help!

I have a massive workbook that I have linked with several other workbooks on a network drive. I have the following formula:

='M:\Financial Aid\John''s Stuff\Win-Loss\[Grids 16-17.xlsx]NEW (FR&TR)'!$D7

This allows me to pull data from that spreadsheet into my current spreadsheet and analyze three years worth of data. However there are 48 columns of data and 12 worksheets within the workbook (almost 100k cells of data) so it takes a long time to go in and update all of those formulas (by column using search and replace) every time I want to look at a new year. I created a separate worksheet within the workbook called "VALUES" & tried to modify the formula to this:

='M:\Financial Aid\John''s Stuff\Win-Loss\[Grids "&VALUES!B2&".xlsx]NEW (FR&TR)'!$D7

I tried several variations of this string but could not get the formula to work

I want to be able to change the "16-17" in the first formula and have the entire workbook update based on the year I am trying to look at. Please help & thank you so much!

 

[Tetra201]

You have to use INDIRECT to achieve this, i.e.:
=INDIRECT("'M:\Financial Aid\John''s Stuff\Win-Loss\[Grids "&VALUES!B2&".xlsx]NEW (FR&TR)'!$D7",TRUE)
Caveat: you also have to have the "Grids XX-XX.xlsx" workbook open; otherwise, you will get a #REF!.

 

[john316swan]

Thank you Tetra201. That works, but do you think there is a way I can combine both INDEX & INDIRECT?

 

[Tetra201]

You are welcome.
An example would help answer your "combine" question.