I've an stand out workbook (Global.xlsx) which consists of hyperlinks (=HYPERLINK(...) ) with other workbooks (representing experiments I have taken, named Exp1, Exp2, etc.), plus some metadata that describe the experiments. Global.xlsx should act as a collector of the most basic informations in the more specific workbooks (a kind of database).

Now I must copy in Global, these values from cells within the ExpX workbooks, but utilizing the same path utilized by the web link (therefore if I alter the destination within the hyperlink, all of the values are up-to-date in the pointed file).

So, you can extract the destination from an hyperlink and employ it inside a formula, in ways that altering the hyperlink even the values change?

Plan of my idea:

  • Many experiment workbooks, all with similar template
  • A outlining workbook, with links to those files, plus some values selected in the linked workbooks

I attempted to make use of the INDIRECT and OFFSET functions, however these can refer simply to opened up files and giving just the title from the file the very first requirement is nice, I'm able to keep your file open when upgrading, as well as the second I must select the filename in the complete path...is it feasible?

This is really a connect to a google document which should provide the idea

The prospective remains to possess one only parameter to alter for every file

I do not think you are able to extract the road in the Hyperlink function without needing VBA. However, place the your pathways in other cells and employ them in Hyperlink (e.g., =HYPERLINK(B1,"Test File"), where B2 consists of the hyperlink). Then, your other functions can reference that same link string and there's only somewhere you have to update it.

So, my first thought would be to give a worksheet to Global.xlsx and employ it to carry a listing of all the links that you'll be using. Then just reference individuals cells for the reason that list inside your Hyperlink functions.