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

john316swan

New Member
Joined
Oct 13, 2016
Messages
40
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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,471
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

New Member
Joined
Oct 13, 2016
Messages
40
Thank you Tetra201. That works, but do you think there is a way I can combine both INDEX & INDIRECT?
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,471
You are welcome.
An example would help answer your "combine" question.
 

Forum statistics

Threads
1,081,702
Messages
5,360,743
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top