Skip to content

Excel Functions

Excel f(x)s = Excel Functions

  • Show developer tab Excel User tips
  • 6174 Kaprekar’s constant Basic Math
  • Edit directly in cell Excel User tips
  • Why I ran from =INDIRECT() function Formulas - combined functions
  • Pixel Excel Drawing Format Cells
  • Scorring, saving multiple outputs in 1 number Basic Math
  • LastSunday, LastSaturday Date and Time
  • Excel Functions = XL f(x)s XLfxs

Links between two workbooks – dynamically using functions

Posted on September 26, 2016 By ANmar

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 cell in Workbook2), we usually just do links like this…

Go to workbook1, select a cell, type in ‘=’ then go to Workbook2 and select a cell, then ‘ENTER’

To get something like this

link1

Which will look like this if try1.xlsx is closed

link2

However, if you open try2.xlsx alone before you open try1.xlsx, you get this message

link3

 

This means now try2.xlsx and try1.xlsx have a link between them

In another word, try2.xlsx liknks to try1.xlsx

Which is ok, but

Except, I don’t like that,

It will take me some time to refresh the link if I have moved that try1.xlsx to another location or rename it.

So, what the solution?

Dynamic link

Which basically, create a link between those two without the mess, and Excel will not recognize it as a link and will stop complaining.

link7

How?

Using the function =INDIRECT(), one of my favorites.

So, in above example, we break in the link inside C12 into pieces, like below

link4

So, C4 has the full folder location of our workbook try1.xlsx

C5 has the file name

C6 has the sheet name

And C7 has the cell

Now, we combine them back into full path as we found it originally into cell C8

link5

By adding the required characters to make it look exactly how INDIRECT expects it

Then, finally, we use it inside INDIRECT

link6

And here is the dynamic link

What is the gain?

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

That particular workbook, has to be open to read the value.

 

What are other gains?

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

 

 

INDIRECT, Lookup and References, Standard functions

Post navigation

Previous Post: VLookup
Next Post: Multiple lines in cell using functions

Related Posts

  • Excel Dynamic Drop-Downs that grow with you COUNTA
  • Validation based on another Validation COUNTA
  • 0% interest promotion Basic Math
  • CountIf limitation COUNTIF
  • Multiple Dual-Validations COUNTA
  • Middle name + 1st name Basic Math

Recent Posts

  • Stale values (Scratched functions)
  • Middle name + 1st name
  • LastSunday, LastSaturday
  • Paste Special Percentage
  • Jan8 date format

Archives

Categories

  • Array formula (1)
  • Formulas – combined functions (58)
  • Google Sheets (2)
  • Non-functions (36)
    • ActiveX controls (2)
    • Conditional Formatting (7)
    • Data Validation (7)
    • Excel User tips (19)
    • Format Cells (6)
    • Graphics (4)
    • Names (3)
  • Standard functions (82)
    • Basic Math (18)
    • Date and Time (16)
      • DATE (9)
      • Hour (1)
      • MONTH (7)
      • NETWORKDAYS (2)
      • TODAY (7)
      • WEEKDAY (8)
      • WEEKNUM (1)
      • YEAR (8)
    • Engineering (1)
      • CONVERT (1)
    • Information (6)
      • CELL (4)
      • ISERROR (1)
      • N (1)
    • Logical (30)
      • AND (5)
      • IF (24)
      • IFERROR (6)
      • IFNA (1)
      • IFS (1)
      • ISERROR (1)
      • OR (2)
    • Lookup and References (36)
      • ADDRESS (4)
      • CHOOSE (3)
      • COLUMN (7)
      • FORMULATEXT (1)
      • HLOOKUP (1)
      • HYPERLINK (2)
      • INDEX (5)
      • INDIRECT (6)
      • MATCH (12)
      • OFFSET (15)
      • ROW (11)
      • VLOOKUP (5)
    • Math and Trig (24)
      • ABS (1)
      • CEILING (1)
      • COUNT (1)
      • COUNTA (4)
      • INT (9)
      • LARGE (2)
      • MAX (1)
      • Min (2)
      • SMALL (3)
      • SUM (3)
      • SUMIF (1)
      • SUMIFS (2)
      • SUMPRODUCT (1)
    • Statistical (5)
      • COUNTIF (4)
      • COUNTIFS (1)
    • Texts and Strings (24)
      • CHAR (5)
      • CONCATINATE (1)
      • FIND (1)
      • LEFT (8)
      • Len (4)
      • MID (9)
      • REPT (3)
      • RIGHT (2)
      • SEARCH (9)
      • STRING (1)
      • SUBSTITUTE (2)
      • TEXT (4)
      • TRIM (2)
      • VALUE (2)
  • Uncategorized (2)
  • User-Defined f(x)s = UDF (3)
    • HyperlinkOf (1)
  • Worksheet (16)
  • XLfxs (2)

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
  • Unix DateTime Number Basic Math
  • Shapes with dynamic output Excel User tips
  • Show developer tab Excel User tips
  • Multiple Dual-Validations COUNTA
  • Formula Beautifier Excel User tips
  • Rotate table using 1 formula ADDRESS
  • SimpleANBox Basic Math
  • Recover the unrecoverable Excel User tips

Copyright © 2025 Excel Functions.

Powered by PressBook News Dark theme