Change file path in formulas for every cell in a sheet

I have a master workbook that is referencing data from several other workbooks whose file names are based on the years data was taken. In the master work book on any given sheet, I would like to be able change the data being used in the formula calculations by simply changing the year value in a single cell. By changing the date value, the formulas are directed from one data workbook to another.

e.g. in the master workbook in Sheet1, if you enter 2008 into cell A1, all of the formulas in Sheet1 now pull values from 2008.xls; if 2008 was replaced with 2009 in cell A1 on Sheet1 of the master workbook, then all of the formulas on Sheet1 would now pull values from 2009.xls.

As I am completely new to VBA (bought my first book last night), I am looking for any help that is out there. I am not entirely new to programming (used C++ to write numerical simulations) but I have zero experience writing macros and the like. If I have not been entirely clear in the description, please let me know.

I think I follow your description, can you post sample workbooks?

hi Jack,

Welcome to the Forum

Are you open to non-macro approaches...
Here are a couple of approaches which don't require any vba, although you could record a macro [alt + t + m + r] of your actions & then modify the recorded code. Note, the Recorder may record some unnecessary code which we can remove for you if you post a recorded macro.

1) How many different workbooks are linked?
If there aren't many, *, use Edit - Links & use change source on each of the files shown.
If there are lots, use the macro recorder to record a couple & post the code for them with explanation of the folder path structure (ie are they all in the same folder for each respective year?).

2) Turn your calculation to manual via Tools - Options - Calculation (esp if you have links to lots of files or lots of links), *, use find & replace (with Sheet/formula settings), & then REMEMBER to put your calculation back to how it was initially set.

*(It may help if you open the files that you want to change to, before trying either of these steps)

3) Lookup the Indirect function in Excel's help files. This function would allow you to use a cell reference to define the year. However, as the Help files state ,

the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

and I'm not sure if this will suit your context...?

... I wrote the above a few hours ago before seeing your zipped files, & now that I've seen the files...

I can see there is only going to be one file referenced & the use of Indirect may be plausible. Would you be open to incorporating the data from the annual files on their own sheets within the Topology file?

If it is important for them to stay in separate files, I suggest adding two helper columns which will help limit the number of external references by moving the duplicated references into the helper columns ie:
- Insert 2 columns on the left of the sheet,
- in the new cell A5:

Please Login or Register  to view this content.

- in the new cell b5:

Please Login or Register  to view this content.

- in the new cell c5:

Please Login or Register  to view this content.

- in the new cell d5:

Please Login or Register  to view this content.

- in the new cell e5:

Please Login or Register  to view this content.

With this setup, you only have 2 fifths of the original amount of "external references" which will make it much easier to use indirect (or any of the other approaches).

hth
Rob

hi,

Yes, please upload a recorded macro of your actions (from memory, I think the recorder will correctly record the action of changing links & we can make it more flexible).

Can the source data be included in the same workbook as the calculations?

Rob

To address your question: no, the source data must remain separate from calculations, according to my instructions, so I cannot combine them. The following is the macro that I have recorded:

Please Login or Register  to view this content.

Hello jackthefork,

Welcome to the Forum!

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code
1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.

Use the Bulletin Board Code Tags
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] at the end of the last line.


To learn more about BB codes used in this forum, just click on the link below...

Bulletin Board Codes Tags

hi,

Hopefully the attached file will work as desired - I've provided both a button & a event macro which runs when there is a change made to the yellow cell.
Note, it will only work at the moment if there is only 1 external file being linked, if this is not the case with your real file, please let us know...

I've also added the helper columns as I outlined in my previous post.

hth
Rob

Thanks for the feedback - I'm pleased I could help

LOL, when other files are being attached to it, the code would/will need a complete overhaul!
(I took what I thought was the quick option last time without considering the power of the Replace function.)

...

Please see the attached file for a modified version which should work for any number of links, based on the existing information (note that there is no error checking if a user enters a non-existent year, all I have done is let the macro continue on its merry way!).

If this latest version meets your needs, can you please mark the thread as solved?
Also, feel free to add to my reputation by clicking on the blue scales at the top right of my post - it is appreciated

Edit: You'll need to change the constants at the top of the ChangeLinks macro by uncommenting your strings & commenting out the strings I used as testing paths.

hth
Rob