| View previous topic :: View next topic |
| Author |
Message |
borges
Newbie
Joined: 22 Mar 2011
Posts: 1
|
 Posted: Tue Mar 22, 2011 2:16 am Post subject: Linking to other files with VLOOKUP |
 |
|
Hi,
I installed OpenOffice for the first time yesterday. One thing which looks different from excel is treatment of information in other files. I handle a lot of BOMs (bills of material) where a common item_numbers.xls file is referenced by several BOM.xls files. This functionality is important, and I can't risk messing up item_numbers.xls.
In excel, as soon as I change something in item_numbers.xls it changes in the referenced spreadsheet too. I did not experience this in Calc. Instead, things took very long to open.
Any suggestions? Can I expect this to work with Calc?
An example equation is:
= VLOOKUP($A24,[item_numbers.xls]Item_numbers!$A:$L,7, FALSE)
Best regards,
Borge |
|
| Back to top |
|
 |
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1851
Location: Sydney, Australia
|
| Posted: Tue Mar 22, 2011 4:37 am Post subject: |
|
|
Say item_numbers.xls is in the "Documents" folder which is in the "borges" folder which is in the "Users" folder on your "C" drive.
Also, say that the Item_numbers sheet has the columns A to L data down as far a row 1000 (Calc doesn't use the $A:$L whole column reference).
Try this formula...
| Code: | | =VLOOKUP($A24;DDE("soffice";"C:\Users\borges\Documents\borges";"Item_numbers.$A$1:$L$1000");7;0) |
as the Calc version of | Code: | | =VLOOKUP($A24,[item_numbers.xls]Item_numbers!$A:$L,7, FALSE) |
When done this way, any changes you make in Item_numbers.A1:L7 are detected by the VLOOKUP formula without having to update links or anything else like that.
If you are having trouble figuring out any of the DDE function's parameters you can copy Item_numbers.$A$1:$L$1000 then use Paste Special with the "Link" option on the Paste Special dialog selected to paste a link into the doc with the VLOOKUP formula. This results in the DDE function as an array formula from which you can read/copy the correct DDE function syntax.
Ken Johnson.
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
