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 <[email protected]>
*To:* [email protected]
*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: [email protected]
<mailto:[email protected]>
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: [email protected]
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
