LibreOffice Calc - Programming Project - Search cells
 

Hi,
I am researching for a scripting project using LibreOffice's Calc built in language. First, I never programmed in calc before and I don't know how to access the scripting screen? Can anyone help? I read some online so i know the language exists.

anyhow, Let me tell a little about the project. Is it possible to check an array of cells for key words and return the value of a cell from a table or column?
for Example.

I have these financial entries

A B C
AMOUNT DESCRIPTION FORMULA
$12.00 MINT CAFE 04598792 ?
$13.00 Kelly's 12892652 ?
#15.57 WALGREENS 475743920 ?

TABLE:

IF CONTAINS THEN CATEGORY

IF CATEGORY
MINT LUNCH
WALGREENS GROCERIES
Kelly's[/TAB]LUNCH


The is to teach calc to determine and categorize my expenses. I think a hlookup or lookup or index command should work the problem is I can't find any documentation on calc's programming language or abilities. like I said, I don't even know where the screen is lol.. I am an import from visual basic so I know I can do this in Excel visual basic but I am never going back to windows...Its the penguin for me or bust!

any one have any ideas how to accomplish this?

I prefer to do this in a script rather than nested functions because I have this program using xmacro that downloads all my different accounts into a master table and it needs the cells blank..its an unattended file.

Since I can't get my example above to display correctly, let me describe my project example in words:

lookup "walgreens" in cell a1 in a table and return the proper category. in this case "Groceries"

The problem here is its not a simple if command or hlookup/vlookup because the program will use a table with preset transacdtion key words in it and compare that table to the contents of cells a1 and return the category from that table.

so I am trying to teach calc to "think" and return the category from a list of examples I give it.

I know its not too clear but I can't get the diagram above to display properly
sorry. Basically, how would you write a script that takes 1000 transactions for example and categorize them automatically? Remember it has to look in cell a1,then categorize it, then go on to cell a2,a3,a4 and so on until it comes to an empty cell then stop. I guess that would be a FOR command in calc? Thanks for reading this :-)

I've always found scripting in Calc to be a bit of a pain. You might look into exporting it into some other data format, such as csv and then pulling it into R or an SQL database. Those are much more powerful scripting platforms, and I know for a fact that the W3C Schools tutorial on SQL/PHP covers exactly the problem you describe.

Unfortunately, I don't do much scripting, so I can't offer much insight beyond that

If I understand you correctly, you want to write Macros. Go to the Tools Menu and then open the Macros item and have a look around to find what you want.
jdk

Thanks because I also find calc script painful, I am going to try out your idea to ease the pain.
thanks again Shane!

---------- Post added 06-18-13 at 02:00 PM ----------

Jdkaye,

Thank you I did these steps and found the module!

if nesting
 

I decided to not strugle with scripting and go with a nested if formula.

I would like to keep adding to this formula. i know there is a limit but i think i can be happy with five if formulas nested. the only problem it only seems to work with the first if formula and ignores the rest.. .anyone good with nesting in libre calc??? here is my formula..

IF(SEARCH("HOME",B11),"lunch work", IF(SEARCH("UNION 76",B11),"AUTO:GAS",""))

if cell b11 does not contain the string "HOME" it returns "VALUE". Anyone have an idea why?

also if cell b11 contains "UNION 76" it returns value..

also if i just enter the union part it works :(

wth?
anyone?

What if you tried a a series of if/thens?

IF b11 = home THEN do X
IF b11 != home THEN do Z

We're kind of stepping out Linux proper and into spreadsheet land here. If no repies are forthcoming here, you may want to try a Calc/Excel forum.

Shane, I am using the built in functions not scripting in calc basic and I have to use the search function to find a string in a cell.
I tried just the If and short of typing the entire cell string in (which is different everytime) I couldn't get it to work. the if alone would work if the contents of cell b11 were the same everytime. this is why I nested it with SEARCH. this works for me but only the first nested if/search. I think you are right, I should try the calc forum :) good idea..thankshttps://lqo-thequestionsnetw.netdna-ssl.com/questions/images/icons_lq/icon12.gif

Update:
I am playing around with..

=if(search("home",b2),"lunch work",""),if(search("union",b2),"auto:gas","")

so far i am getting a error 509
which i understanding is missing operator?https://lqo-thequestionsnetw.netdna-...s_lq/icon5.gif

OH, this is where I found a list of errors for calc
http://www.linuxtopia.org/online_boo...ror_codes.html

I just tried the excel equal and it behaves the same way as Linux calc. Only the first half of the formula is evaluated, it totally ignores the second half and returns "value"

=IF(SEARCH("CAPITAL",C2,1)=1,"CREDIT CARDS",IF(SEARCH("MINT",C2,1)=1,"LUNCH WORK",""))

so if I key in "CAPTIAL ONE 123354 DAFSDFAS" in cell c2, I get the proper response "CREDIT CARDS" but if I key in "MINT CAFE" I get
"#value!" The same behavior under libreoffice calc. I also tried nesting SEARCH with the various LOOKUPS and wasn't able to do anything
with those commands.

Moderator, could you please move this thread to the appropriate place? I can't find the thread for calc functions.

Thank you.