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