[KLUG Members] Wozzer Spreadsheet Question (in)

[members@kalamazoolinux.org]

>I have a problem that I ***must*** solve in a spreadsheet for several ugly
>reasons,  but a few pages appear to be missing from the spreadsheet section
>of my spell book.  I see hlookup, vlookup, etc...  but they have collectively
>slapped me up and tossed me out the window.  So I come looking for some
>assistance figuring of the 200+ people here there must be 1 spreadsheet
>guru.
>The spread sheet in question is Star office 6.0,  but I'll need to toss the
>end result out in xls.
>I have columnar data [text1, d] [text2, g] [value, f] on sheet "Data".    I
>need to transform [text2, g] and see if I find a row that matches the 
>transform. I put the transforms and all my crap on sheet "Work". The transform
>is:
>=IF(Data.G2="BRANCH XFER TO PARTS";"BRANCH XFER FROM
>PARTS";IF(Data.G2="BRANCH
>XFER FROM PARTS";"BRANCH XFER TO PARTS";IF(Data.G2="DEBIT  XFER
>CLEARING";"CREDIT  XFER CLEARING";IF(Data.G2="CREDIT XFER CLEARING";"DEBIT
>XFER CLEARING";"ERROR"))))
>So I've constructed a [text3] [text4] sheet #2 which is
>[text3, b]
>=CONCATENATE(Data.D2;":";Data.G2;":";TEXT(ABS(Data.F2);"0000.00"))
>[text4, c]
>=CONCATENATE(Data.D2;":";Work.A2;":";TEXT(ABS(Data.F2);"0000.00"))
>So the only difference betweenm text3 and text4 is that one uses the
>original text, and the second uses the transformed text.
>So text3 looks like 14190580:BRANCH XFER TO PARTS:0004.72
>and text4 looks like  14190580:BRANCH XFER FROM PARTS:0004.72
>Now I need to know all the text4 columns that do not have a match in text3
>(Basically out of balance transactions).  I constructed these horrid keys as
>it doesn't appear to me (spreadsheet neophyte) that the lookup functions can
>handle more than one condition.
>But how does one USE the lookup functions,  I can't find one decent
>example!

Ok, I hate spreadsheets.  But I got it to work.  But a couple of interesting points:

1 - VLOOKUP only works correctly in Excel, the formula would not work in Open
Office or Gnumeric
2 - The formula editor in both Excel and Gnumeric dies on long/complicated very
nested formulas.  The editor in Open Office stands firm and works great!
3 - The VALUE function only works correctly in Open Office,  in Excel and
Gnumeric it errors on signed values (example: "15.50-",  OO will turn into
-15.5, while Gnumeric and Excel return #ERR!)
4 - VLOOKUP on tables generated with forumlas don't work in OO at all,  it
simply doesn't find anything.  Both Gnumeric and Excel crash - eventually. 
Copying and pasting the table as values made the formula work in Excel,  then
Gnumeric simply returned that the function was in error.

The formulas in question -
Account name re-write
=IF(Data!G2="BRANCH XFER TO PARTS","BRANCH XFER FROM PARTS",IF(Data!G2="BRANCH
XFER FROM PARTS","BRANCH XFER TO PARTS",IF(Data!G2="DEBIT  XFER
CLEARING","CREDIT XFER CLEARING",IF(Data!G2="CREDIT XFER CLEARING","DEBIT  XFER
CLEARING","ERROR"))))
Provided Transaction Key
=TRIM(CONCATENATE(Data!$D2,":",TRIM(Data!$G2),":",TEXT(VALUE(Data!
$F2),"00000.00")))
Generated Transaction Key
=TRIM(CONCATENATE(Data!$D2,":",TRIM($A2),":",TEXT(-VALUE(Data!$F2),"00000.00")))
Search result
=VLOOKUP(B1,$A$1:$A$12575,1,FALSE)