Libreoffice : Referencing cells in another worksheet via FIND(), LOOKUP(), or EXACT()?

Question

I'm trying to learn LibreOffice's scripting capabilities, and have a specific scenario that I haven't been able to figure out.

What I'm trying to do is get the value from another worksheet, by doing a search for a specific value in an adjacent cell.

For example, lets say I have two worksheets:

Worksheet1

And Worksheet2

What i'm trying to do, is populate each value in Worksheet1, Column B with the associated value from Worksheet2, Column B. The way I'm trying to do this is to write a statement that finds and uses the month in Worksheet1 as the search criteria against Worksheet2, Column C.

So far I haven't had much luck, but here's what I've been trying to work with:

='Worksheet2'.$C.FIND('Worksheet1'.$A1).$B1

This is more than likely wrong, but I'm trying to express the logic, which is "in Worksheet2, find the value from Worksheet1:$A1, and give me the value from Worksheet2:$B1"

Essentially what it comes down to is that I do not yet understand the syntax and paradigms of this language.

Any thoughts on how I can accomplish what I'm trying to do above?

Also, I would be interested in any links for online tutorials about this language (LibreOffice Basic?)

Thanks in advance!

Solution 1

There is a function called VLOOKUP that you might want to try.

Syntax for your example would be:

=VLOOKUP(A1,'Worksheet2'.B1:C12,1)

Solution 2

I just adapt a routine of mine to your problem. It should work... Assuming both sheets don't have empty lines:

Sub MergeSheets

Dim Ind as Integer
Dim oSheet1, oSheet2
Dim oDocument as Object

  oDocument = thisComponent

  ' Destination Sheet
  dSheet = oDocument.getSheets().getByName("Worksheet1")

  ' Origin Sheet
  oSheet = oDocument.getSheets().getByName("Worksheet2")

  oCount = 0
  oCell = oSheet.getCellByPosition(0,oCount)
  while oCell.Formula <> ""

    ' Search for the matching line
    dCount = 0 
    dCell = dSheet.getCellByPosition(2,0)
    while dCell.Formula <> "" and dCell.Formula <> oCell.Formula
       dCount = dCount + 1
       dCell = dSheet.getCellByPosition(2,dCount)
    wend

    ' Set Value to the destination cell
    ' If Key was not found, the last empty line will be used
    fCell = dSheet.getCellByPosition(1,dCount)
    if fCell.Formula = ""
       fCell.Formula = "="+oSheet.getCellByPosition(1,dCount).Formula
    else
       fCell.Formula = fCell.Formula+'+'+oSheet.getCellByPosition(1,dCount).Formula
    end if

    oCount = oCount + 1
    oCell = oSheet.getCellByPosition(0,oCount)

  wend

End Sub