Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index


Hi Tom.

Well, not quite. In my particular case this formula worked. But with some off-list input from smarter brains than mine, I realize it wouldn't work under many situations.

It turns out the formula would need to change a bit, otherwise what I did would leave a trailing semi-colon where column X is blank. In fact, it's a tricky situation b/c a semi-colon is needed after U3 if U3 has a value AND any of the others has a value, otherwise not. Then, if V3 has a value and any of the other later cells has a value, there should be another semi-colon. Etc. etc. In the last case, a semi-colon should follow W3 only if there's a value in X3.

I've tried a bunch of formulas and the closest I've come is the following:

=A13&IF(NOT(ISBLANK(A13))AND(NOT(ISBLANK(B13)))OR(NOT(ISBLANK(C13)))OR(NOT(ISBLANK(D13))),";","")&B13&IF(NOT(ISBLANK(B13))AND(NOT(ISBLANK(C13)))OR(NOT(ISBLANK(D13))),";","")&C13&IF(NOT(ISBLANK(C13))AND(NOT(ISBLANK(D13))),";","")&D13

But that clearly doesn't work. Note I'm working in a test sheet and am using different cells. This formula leaves leading semi-colons under a number of situations and double semi-colons under others. I'm not entirely sure CASE would work any better here though.

Anyone want to help out, great. But it's a pretty specialized situation, so I understand if not.

Carl


On 5/16/13 3:02 AM, Tom Davies wrote:
Hi :)
WoooHooo!! Nicely done! :) Is this whole thread solved now? COngrats of so!
Regards from
Tom :)



    ------------------------------------------------------------------------
    *From:* Carl Paulsen <carlpaulsen@comcast.net>
    *To:* users@global.libreoffice.org
    *Sent:* Thursday, 16 May 2013, 1:44
    *Subject:* Re: [libreoffice-users] "Case" function equivalent in Calc

    At long last I got this to work.  Syntax is "ISBLANK" and not
    "ISEMPTY"
    - aaarrrrgggghhhh.  My Filemaker days are getting in my way.  Replace
    all "isempty" below with "ISBLANK" and it works perfectly now.

    Carl


    On 5/15/13 6:38 PM, Carl Paulsen wrote:
    > So in the absence of a Case function, here's what I've done so
    far as
    > a calculated solution.  Note that the data I want to concatenate
    is in
    > cells U3, V3, W3, and X3.  In Y3, I put the following:
    >
    > =U3 & IF(NOT(isempty(U3)),";","") & V3 &
    IF(NOT(isempty(V3)),";","") &
    > W3 & IF(NOT(isempty(W3)),";","") & X3
    >
    > The idea is that I put together U3, a semicolon if U3 isn't
    empty (and
    > nothing if it is), V3 and a semicolon if V3 isn't empty, W3 and a
    > semicolon if W3 isn't empty, and X3.  If any of the cells is empty,
    > nothing will be added until the next cell that has data.
    >
    > Unfortunately, I'm getting a #NAME? error.  I'm assuming some
    kind of
    > syntax error.  Any words of wisdom?
    >
    > Thanks a ton all.
    > Carl
    >
    >
    >
    > On 5/15/13 5:13 PM, Dan Lewis wrote:
    >> On 05/15/2013 04:16 PM, Carl Paulsen wrote:
    >>> Anyone know if there is an equivalent to the Filemaker Pro "Case"
    >>> function?  It's kinda like the "IF" function but is simpler to
    >>> concatenate multiple conditions. It basically says If
    something is
    >>> true then do what is specified, if the next thing is, then do
    that,
    >>> if the next thing is true, do that, etc.
    >>>
    >>> Here's what I need to do.  Take 4 columns and concatenate with a
    >>> semi-colon between the values, but not string together two
    >>> semi-colons consecutively.  Like:
    >>>
    >>> Phone    Email    Mail -> Phone;Email;Mail
    >>> Phone                  Mail -> Phone;Mail
    >>>                            Mail -> Mail
    >>>              Email    Mail -> Email;Mail
    >>>
    >>> So semi-colons only occur if there's a value present and not
    at all
    >>> if there's only one value present.
    >>> I hope that makes sense and displays correctly.
    >>>
    >>> Carl
    >>      I just checked the available functions in Calc. The "Case"
    >> function does not appear among them. "Case When" is available
    in Base
    >> database queries though.
    >>
    >> --Dan
    >>
    >
    >

--
    Carl Paulsen

    8 Hamilton Street

    Dover, NH 03820

    (603) 749-2310


-- To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
    <mailto:unsubscribe@global.libreoffice.org>
    Problems?
    http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
    Posting guidelines + more:
    http://wiki.documentfoundation.org/Netiquette
    List archive: http://listarchives.libreoffice.org/global/users/
    All messages sent to this list will be publicly archived and
    cannot be deleted



--

Carl Paulsen

8 Hamilton Street

Dover, NH 03820

(603) 749-2310


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.