Updating a link in a file extension dynamically based on a field in that spreadsheet

Hi guys

I am new to the forum and have been searching around to see if I could figure this out but can't. Any help would be greatly appreciated.

I have a file that is pulling one value from a field in a closed file. Link below:

='W:\Client\C\Client name\2013\Test File\Test Name\filedname\List\[filename.xls]test'!$H$60

What I am trying to do is replace the \fieldname\ with a cell from the sheet so it will dynamically pull a new value when that field is updated.

So if G7 has the link ='W:\Client\C\Client name\2013\Test File\Test Name\TEST1\List\[filename.xls]test'!$H$60 and in the same sheet if B2 has the value TEST1, when I change B2 to TEST5 that the link changes to ='W:\Client\C\Client name\2013\Test File\Test Name\TEST5\List\[filename.xls]test'!$H$60

I really appreciate it. I believe it is one value. with a dependency to that field that will be dynamic.

Which cell has the folder name?

It is in L7

You are the best!!! Thanks

Just a question regarding this, and anyone can reply. How do I see the formula or function that is running. I would like to learn from what you did so I know how to do it in the future.

You need to enter the world of VBA. From Excel, open the VBA development window by pressing ALT-F11. On the left is a a hierarchy that lists open Excel files, and under each Excel file it lists VBA modules under a folder called "Microsoft Excel Objects". Each of the objects listed underneath is a module which can contain code. Every Excel workbook by default has one module for each worksheet (Sheet1, Sheet2, etc.), plus one module for ThisWorkbook. You can also add new modules that get default names like Module1, Module2, etc.

Find your file in that list, and look at the module named for the worksheet named July, which is the only sheet in your book. Double click on it and you can see the code in the window on the right. It's a bit of a leap to go from not knowing VBA or programming to understanding how all this works. There are many resources available paid and free for learning about VBA. I don't want to try to teach a course in this thread but I can answer specific questions.