{"id":555,"date":"2016-09-26T19:37:45","date_gmt":"2016-09-26T19:37:45","guid":{"rendered":"http:\/\/xlfxs.com\/?p=555"},"modified":"2016-09-26T19:37:45","modified_gmt":"2016-09-26T19:37:45","slug":"dynamic-links-between-two-workbooks","status":"publish","type":"post","link":"https:\/\/xlfxs.com\/?p=555","title":{"rendered":"Links between two workbooks – dynamically using functions"},"content":{"rendered":"

I don’t believe I don’t have this here yet. This is one of my oldest tricks<\/p>\n

When we have to do links between two workbooks (One cell in Workbook1 to have value updated from a\u00a0cell in\u00a0Workbook2), we usually just do links like this…<\/p>\n

Go to workbook1, select a cell, type in ‘=’ then go to Workbook2 and select a cell, then ‘ENTER’<\/p>\n

To get something like this<\/p>\n

\"link1\"<\/a><\/p>\n

Which will look like this if try1.xlsx is closed<\/p>\n

\"link2\"<\/a><\/p>\n

However, if you open try2.xlsx alone before you open try1.xlsx, you get this message<\/p>\n

<\/p>\n

\"link3\"<\/a><\/p>\n

 <\/p>\n

This means now try2.xlsx and try1.xlsx have a link between them<\/p>\n

In another word, try2.xlsx liknks to try1.xlsx<\/p>\n

Which is ok, but<\/p>\n

Except, I don’t like that,<\/h3>\n

It will take me some time to refresh the link if I have moved that try1.xlsx to another location or rename it.<\/p>\n

So, what the solution?<\/p>\n

Dynamic link<\/span><\/h2>\n

Which basically, create a link between those two without the mess, and Excel will not recognize it as a link and will stop complaining.<\/p>\n

\"link7\"<\/a><\/p>\n

How?<\/h3>\n

Using the function =INDIRECT(), one of my favorites.<\/p>\n

So, in above example, we break in the link inside C12 into pieces, like below<\/p>\n

\"link4\"<\/a><\/p>\n

So, C4 has the full folder location of our workbook try1.xlsx<\/p>\n

C5 has the file name<\/p>\n

C6 has the sheet name<\/p>\n

And C7 has the cell<\/p>\n

Now, we combine them back into full path as we found it originally into cell C8<\/p>\n

\"link5\"<\/a><\/p>\n

By adding the required characters to make it look exactly how INDIRECT expects it<\/p>\n

Then, finally, we use it inside INDIRECT<\/p>\n

\"link6\"<\/a><\/p>\n

And here is the dynamic link<\/p>\n

What is the gain?<\/h3>\n

Mostly, the flexibility of chagninng the references, if folder got changed (by moving the workbook) or name got change, by renaming it, or sheet, we can just type it in its box and press enter<\/p>\n

That particular workbook, has to be open to read the value.<\/p>\n

 <\/p>\n

What are other gains?<\/h3>\n

Well, think about dynamically reading numbers from multiple sheets into one, for example, you have sheet per project and you want to collect all these info from all these projects into a ‘summary’ sheet, this dynamic link, can help you build one cell, then copy it to others to apply it there<\/p>\n

 <\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

I don’t believe I don’t have this here yet. This is one of my oldest tricks When we have to do links between two workbooks (One cell in Workbook1 to have value updated from a\u00a0cell in\u00a0Workbook2), we usually just do links like this… Go to workbook1, select a cell, type in ‘=’ then go to…<\/p>\n

Read More “Links between two workbooks – dynamically using functions”<\/span> »<\/a><\/p>\n","protected":false},"author":1,"featured_media":556,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[85,36,6],"tags":[],"class_list":["post-555","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-indirect","category-lookup_and_references","category-standard_functions"],"_links":{"self":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/555","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=555"}],"version-history":[{"count":0,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/posts\/555\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=\/wp\/v2\/media\/556"}],"wp:attachment":[{"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=555"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=555"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlfxs.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=555"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}