I have a cell. In that cell is a reference to a cell in another document.
If I double click on my cell, I get something like " =‘file:///home/me/You/prices.ods’#$Sheet1.A28 "
I would like to capture this text. Not the value represented in this cell. The address itself.
Ultimately, I am looking to create two columns. The user clicks on the PRODUCT, whose name is copied into column A and whose price is simultaneously copied into cell B.
Can this be done???
The link you have will update in different ways. When you open the spreadsheet the following should pop up:
Selecting Yes will update the information even if the other spreadsheet is closed.
If you select No, you cannot update the information unless the other spreadsheet is open.
When the other spreadsheet is open, you can retrieve any changed information by pressing F9 (Recalculate).
You can use a DDE link. Using your sample it would be:
=DDE("soffice","file:///home/me/You/prices.ods","Sheet1.A28")
This has the added advantage of receiving updated information as it happens. So if the other spreadsheet changes, so does this value without intervention.
I don’t see a way to return two columns with one cell function. Just copy it to another cell and change the reference.
Edit: Sorry for the misunderstanding. If you place your formula in A1 you can do a copy of the cell and then Paste->Special of the data (no formula) into C1. You can also do this same process with a macro (generated by Record->Macro):
sub PasteSpecial
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$C$1"
ispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
rem ----------------------------------------------------------------------
dim args4(5) as new com.sun.star.beans.PropertyValue
args4(0).Name = "Flags"
args4(0).Value = "SVD"
args4(1).Name = "FormulaCommand"
args4(1).Value = 0
args4(2).Name = "SkipEmptyCells"
args4(2).Value = false
args4(3).Name = "Transpose"
args4(3).Value = false
args4(4).Name = "AsLink"
args4(4).Value = false
args4(5).Name = "MoveMode"
args4(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args4())
end subThe answer is simple, it turns out: VLOOKUP.
VLOOKUP lets you enter a search term in a box. It will:
(1) find the search term in your data sheet
(2) search across the row where it finds the search term to whatever column you indicate
(3) return the value contained in that column.
Lets say you have a data sheet named ‘Data’ and on that sheet are four columns, D E F G labeled as follows:
D E F G
ITEM QUANTITY GROSS NET
and you are doing calculations on a second sheet (or document) named ‘Work’ and you would like to be able to enter search terms in column A of ‘Work’ and have column B of ‘Work’ magically fill with the ‘NET’ values found in column G of ‘Data’.
Proceed as follows:
(1) column A of 'Work' is left blank. This is where you will enter search terms
(2) column B of 'Work' gets the VLOOKUP formula in the following format:
=VLOOKUP(<imput cell>;<data range>;<column where answer is>;<exact match T/F>)
There are four pieces of information that have to be specified. The first is:
<imput cell> is the cell in 'Work' where you put your search term -- 'A1' 'A2' etc.
second:
<data range> is the range of cells in 'Data' containing the terms to search for AND the
data to return. For example, if the first item in Data, columnD is at row3,
and the last item in columnG is in row80, the range would be Data.D3:G80
If this information is in an entirely different document, you can open that
document and select the range just like you would do if the information
were in your working document.
third:
<answer> is how many columns over the program should look from where it finds the search term. If you know it is going to find the term in column D and that the answer you want back is in column G, enter '4'.
fourth:
<exact> this is important. The algorithm used by VLOOKUP is lazy. It only looks for an exact match to your search term if you tell it to. On my version of LibreOffice, Yes=0 and No=1. You want '0'.
Now, if you enter a search term at A1, say ‘bubblegum’, B1 will fill with the net cost of that item – say, ‘0.50’.
And that’s all there is to it.
I would be happy with one of two options.
Option 1: capture the address of the precedent document/cell,
" ‘file:///home/me/You/prices.ods’#$Sheet1.A28 " – this actual phrase (address) NOT the data it refers to.
Option 2: In document A, sheet A, cell c6, I copy from document B, sheet D, cell za93. A reference to the information stored in B.D.za93 is transferred into A.A.c6. NOW, I’d like to format cell A.A.d6 such that IT picks up the data at B.D.zb93 and dumps it in A.A.e6.