Date: prev next · Thread: first prev next last
2014 Archives by date, by thread · List index

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

Hope this helped.


On Thu, 20 Feb 2014 01:17:47 -0800 (PST)
IGraham <> 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)


W764  LibreOffice
View this message in context:
Sent from the Users mailing list archive at

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.