Ask Your Question
0

click one cell and copy and paste two?

asked 2017-02-12 04:51:35 +0200

micheletrecaffe gravatar image

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????

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-02-12 06:37:11 +0200

Ratslinger gravatar image

updated 2017-02-12 08:16:59 +0200

The link you have will update in different ways. When you open the spreadsheet the following should pop up:

image description

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 sub
edit flag offensive delete link more
0

answered 2017-02-12 06:56:33 +0200

micheletrecaffe gravatar image

updated 2017-02-12 22:51:27 +0200

The 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.

edit flag offensive delete link more

Comments

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.

micheletrecaffe gravatar imagemicheletrecaffe ( 2017-02-12 08:44:16 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-02-12 04:51:35 +0200

Seen: 93 times

Last updated: Feb 12 '17