Re: [libreoffice-users] Re: Calc - sort in to columns

Just tried something that *may* work for you: pivot tables.

On your sample spreadsheet, I did the following:

* Select all the data, header row included.
* Select "Data | Pivot Table | Create".
* With "Current selection" selected, click "OK".
* Drag "member type" and "email" to "Row Fields".
* Click "OK".

A new sheet is created with the pivot table, which has the data sorted
by member type, with all the email addresses listed per type.

Note that if data is added, it won't automatically show up in the pivot
table. You can right-click in the pivot table and select "Edit Layout",
then click "More" and change the "Selection from" value to include the
new rows of data. If you set the last row to be beyond the end of the
current data, you will get a new "member type" in the pivot table
called "(empty)", but any data now added in empty rows that are still
within the pivot table selection will show up if you right-click on the
pivot table and select "Refresh".

This isn't really what pivot tables are for, and may not suit your
needs, but given what you've described of your needs, this may be a
sufficient workaround.

You haven't really given us a proper explanation of what you are trying
to do, so it's hard to guess what might suit your needs best, but it
does sound like a database would be the best solution here, or perhaps
user defined functions (i.e. coded macros) in Calc. Short of that,
I think that you'll probably need to manually maintain a list of
email addresses per member type, either in a separate file, or still
manually in your columns as per the example file. Even using a pivot
table, you'll have to either adjust the selection when data is added,
or make the selection bigger than the data and just refresh when data
is added, and hope the data doesn't go beyond the end of the selection,
in which case you'll still need to adjust the selection.

Maybe if you give us more details we can come up with better
suggestions.

Hope this helped.

Paul



On Thu, 20 Feb 2014 01:17:47 -0800 (PST)
IGraham <graham@lovatt.co.uk> wrote:

> Hello and thanks to Alan & Brian (who replied private)
>
> Yes my choice of words to describe the problem wasn't great, 'sort'
> was the first thing I tried so was probably the first to swim up from
> the murk.
>
> The mock sheet shows a problem from a larger sheet saved as a .xls,
> stored within Dropbox and accessed and added to by other people - all
> of whom have limited spreadsheet knowledge. Anything I add to the
> sheet has to add minimum chance of giving a user the possibility of
> screwing things up. Which was why I was keen to present the member
> type emails in separate columns to just be used.
>
> So the first thing I tried was 'Auto filter', that worked but the
> user still had to select the relevant from up to 60 rows (not a big
> problem I suppose). However if a user sorted the sheet and
> subsequently saved the sheet then the next user would be presented
> with a sheet they hadn't seen before, do a bit of a panic and
> possibly try to add data in the wrong place. I understand filters,
> they 'might' but best not to wear rose tinted glasses.
>
> I also tried 'If' statements. Consternation (include a 'index column +
> member type = unique) and VLOOKUP. Neither of which gave good result.
>
> I was trying to set something up that wouldn't mean extra and ongoing
> work for me, but maybe just maintaining a simple text file is a way
> to go. Possibly this whole 'member details' sheet would now be better
> served by a database. But I'm not great at database stuff and I don't
> know what my users would make of it.
>
> If you have any other solutions/thoughts I'd be grateful (theres shed
> loads of spreadsheet stuff (I know) I don't know)
>
>
>
> -----
> IGraham
>
> W764  LibreOffice 4.2.0.4
> --
> View this message in context:
> http://nabble.documentfoundation.org/Calc-sort-in-to-columns-tp4098035p4098086.html
> Sent from the Users mailing list archive at Nabble.com.


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