How can links to other workbooks, including vlookups, update dynamically?

How can links to other workbooks, including vlookups, update dynamically?

Dynamic links across spreadsheets: Imagine you want to use a vlookup from a spreadsheet database (that is, a range of cells you are treating as database), and the database is in another workbook. In Excel, changing data in the database will cause the vlookup to update without user action (dynamic updating).

If you open such a formula into Calc, the formula looks like this:

=VLOOKUP($B$16;'file:///Users/tris/Documents/Cust_database.ods'#$sheet1.$A$1:$IV$65536;4;0)

but this does not update dynamically.

You need to save the database document with any changes made to it, and then manually refresh links perhaps by closing and reopening the spreadsheet using the vlookup.

Solution

Copy the range of cells in your database, and then do a Paste Special -> Link The formula will look like this

={DDE("soffice";"/Users/tris/Documents/Cust_database.ods";"sheet1.A3:I281")}

This is a dynamic link. References to the other workbook can be changed to use this "DDE" reference, rather than the "file:///" reference.

So, you can rewrite the vlookup as this

=VLOOKUP($B$16;DDE("soffice";"/Users/tris/Documents/Cust_database.ods";"sheet1.A3:I281");4;0)

and now you have a dynamic vlookup

Works in version 2.2