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

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


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