Re: [libreoffice-users] Help with Conditional Macro Please

On 26/10/14 20:43, Brian Barker wrote:
> At 19:44 26/10/2014 +0000, Alastair Noname wrote:
> > I have been working on the easy bits of my problem and have a solution
> > except that the answer looks dreadful as I cannot find out how to
> > insert spaces or commas into results of the formula to get the format
> > presentable. I am using:-
> >
> > =CONCATENATE(C2,D2,TEXT(E2,"dd/mm/yy"),TEXT(F2,"dd/mm/yy))
> >
> > I hope I can get the results looking better.
>
> Easy: =CONCATENATE(C2,",",D2," ",...
> (Actually, I find the alternative =C2&","&D2&" "&... preferable.)
>
> > That leaves me with the need to add the "conditional" element but
> > LOOKUP may be the solution as just by chance the elements /2 become
> > 1,2,3,4 as another subscriber has seen.
>
> I think you are confusing two alternative techniques. His trick enables
> you to use the CHOOSE() function. You could use VLOOKUP() from a table
> whatever the key values were.
>
> > On last question if I get it all worked out is how may I "freeze" the
> > formula results. I need to do this as I must remove a column used in
> > the formula before exporting as .csv for importation into an
> > accounting programme.
>
> There are various techniques:
>
> o Copy the result column. Paste it back over itself, but using Edit |
> Paste Special... (or Ctrl+Shift+V) instead of ordinary Paste. In the
> Paste Special dialogue, ensure that Formulae is not ticked.
>
> o Put the unwanted column on a different sheet (before you create your
> formulae) - so only the material you eventually want to output is on the
> single sheet that will be saved to your .csv file.
>
> o Copy the required material to appropriate columns on a new sheet
> (which you will eventually save as the .csv file). When copying the
> result column, use Paste Special, this time with both Formulae and Link
> ticked.
>
> I trust this helps.
>
> Brian Barker
Hi Brian,
OK I now have the spaces and other marks sorted out.  Not helped by the 
"Intelligent" quotation marking in libreoffice.  I must find out how to 
turn off.  I used CONCATENATE and CHOOSE and now results look OK.

Before I get round to clearing the working columns as you suggest I have 
a problem with date format which I have forgotten how I solved.

In short the dates are in columns as such as "18 Sep 2015" without 
leading comma so they are numbers not text.  However I cannot change the 
format.  I think last time I did this I created another column with 
correct date format and used paste special to copy but this time it does 
not work.  Any suggestions please?

Budgie.

--
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