Difference between revisions of "Documentation/FAQ/Calc/Formatting/How can links to other workbooks, including vlookups, update dynamically?"
(New page: {{DISPLAYTITLE: How can links to other workbooks, including vlookups, update dynamically?}} <section begin=question/> '''How can links to other workbooks, including vlookups, update dynam...) |
m |
||
| Line 12: | Line 12: | ||
If you open such a formula into Calc, the formula looks like this: | 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)''' | + | <code>'''=VLOOKUP($B$16;'file:///Users/tris/Documents/Cust_database.ods'#$sheet1.$A$1:$IV$65536;4;0)'''</code> |
but this does not update dynamically. | but this does not update dynamically. | ||
| Line 21: | Line 21: | ||
'''Solution''' | '''Solution''' | ||
| − | Copy the range of cells in your database, and then do a Paste Special | + | Copy the range of cells in your database, and then do a {{menu|Paste Special|Link}} |
The formula will look like this | The formula will look like this | ||
| − | '''={DDE("soffice";"/Users/tris/Documents/Cust_database.ods";"sheet1.A3:I281")}''' | + | <code>'''={DDE("soffice";"/Users/tris/Documents/Cust_database.ods";"sheet1.A3:I281")}'''</code> |
| Line 31: | Line 31: | ||
So, you can rewrite the vlookup as this | So, you can rewrite the vlookup as this | ||
| − | '''=VLOOKUP($B$16;DDE("soffice";"/Users/tris/Documents/Cust_database.ods";"sheet1.A3:I281");4;0)''' | + | <code>'''=VLOOKUP($B$16;DDE("soffice";"/Users/tris/Documents/Cust_database.ods";"sheet1.A3:I281");4;0)'''</code> |
and now you have a dynamic vlookup | and now you have a dynamic vlookup | ||
Latest revision as of 16:49, 16 May 2021
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
