A single spreadsheet with off-shoots?!

[oxcat1]

I wonder if you can help?

I volunteer in an institution and need help maintaining their spreadsheets. There is a mailing list, and then various other spreadsheets representing members of various 'clubs', 'societies' etc etc. Is there any way of keeping them all 'connected' so that a single update updates them all?

For example, occasionally I commit the heinous crime of sending something out from the main mailing list to somebody who has died. I receive acknowledgement of that death and remove the person from the mailing list spreadsheet. However, sometimes I don't know that they were also a member of the 100 Club for example, and so their relatives receive yet another upsetting letter when the next mailing for that goes out. Similarly, when I receive a change of address I can have to update the details four or five separate times, which seems crazy.

There must be an easier - and more foolproof! - way of doing this?!

I think you should consider a 'master list' which has first column showing name, second address or DoB (in case two people have same name!) and then further columns indicating membership of each group.

That way, the 'master list' can be consulted when someone dies, and you know which groups they belonged to, and can remove them from every list, at that point. It gets away from messing up with the existing lists.

In reverse, when someone joins a group, you check the 'master list' first in case they exist already (and if so, add a marker for the group they have just joined {could be something useful like month and year}, or if they are not found, add them to the 'master list', then add them to the group's individual spreadsheet

It could be done in a different way with a database, but would be considerably more work.

To build the 'master list' I'd suggest making a copy of each 'group' spreadsheet as a CSV. It would then be possible to merge those together and sort them by name. Depending on how urgent this is, I could perhaps help you if you feel it's too hard or have no easy way to search / sort etc.

To avoid the multiple amendments problem, would probably require a change to a database (unless some spreadsheet whizz comes up with a solution)...

Sorry.

Thank you so much for the reply.

I was wondering if using databases rather than spreadsheets was a solution, but I've always used Excel as found it easiest to mail-merge from that. I've checked and none of the computers here actually have Access (which I'm not very familiar with) so I'd have to buy it, which I'm pretty loath to do unless it will be the solution to all my problems!

Will keep thinking - thanks.

You should have everything linked rather than the same data multiple times. This is a rather sad thing I am slightly too passionate about. So if you have sheet with, say, the names and addresses on, the say 100 club sheet should be drawing the names from the main sheet. That info should not be repeated. That's the central principle, never repeat anything, always link. Then you'd just change on the main, say address sheet, and the others would update.

A good access database would be waaay better but to properly use access you need to understand relational database principles first. It's not just a matter of learning the software. Just doing that means people end up with appalling databases with duplication.

Don't mean to sound overbearing. I'm a bit passionate about data management Would love to get stuck in sorting out your systems. My idea of a fun job.

And the cells with the links in should be locked so that people can't go updating the wrong one.

As NetworkGuy said, a database is the ideal solution; however, a spreadsheet can be operated as a database by following good spreadsheet/database principles (a spreadsheet is in effect a flat file database).

It may be possible to use a single list - this would be the ideal solution as you only need to make one amendment (this is always my goal when designing a spreadsheet, and what it is good for - store data once, and refer to that data when it is needed):

I would recommend each row in the database represents a person - name, address, etc. and also membership of groups - have a column for each group with the group name at the top, and a simple indicator (like an 'X') in the column to indicate they are a member.

With either of these set ups, you can then use Autofilters to select the group (or groups) you are interested in and extract only that information.

One principle to follow that I referred to above: use a separate column for each piece of information; don't combine information that may need to be separated out. For example, don't store something as:

Name: Mr Joe Bloggs

Separate each bit out to give:

Title: Mr
FirstName: Joe
Surname: Bloggs

Same with address, have a column for Address1, Address2, Address3, Address4, PostCode, rather than one column for 'Address'.

If you want any help setting up a spreadsheet drop me a message, I'll give you my email address so you can send a sample file (without anyone's personal data, obviously) and I can put a structure together.

"you can then use Autofilters to select the group (or groups)" ...

There we go... someone better versed in spreadsheet use than myself

and as for removing duplication... yes, Alice, I remember 'Third Normal Form' from 30 years ago... but conversion to a db is not for the faint hearted when it's someone else's data...

I would have them all in one sheet with columns indicating which club they are a member of. You can filter for mailmerges etc. Or am I missing something.

That's what I'd do too.

Mmm I love a good spreadsheet! If you want me to have a go at it, OP, just let me know! (A level ICT teacher here.)

Gosh - I hadn't expected such passionate and animated replies! How exciting!

I'm thinking through all your suggestions - thank you so much. The thing that I am still confused about is how to ensure that a single spreadsheet doesn't become too unwieldy. For example, for those in the 100 Club I need to record details of payments received and payments owed, yet for the children that attend one of the other clubs, I need to have medical details, parents, allergies etc kept on file, information that clearly isn't needed for most people. If I build a single spreadsheet to incorporate all possible columns, it would end up enormous?

Or have I missed something here?

Thanks so much! I didn't expect any replies, let alone any so helpful!

And presumably, if I did it by linking cells, I would still have the same information duplicated, but a change to one sheet (the master) should update all the others automatically?

Is that right?

Bringing in payment details into the equation would really push me towards a relational database (one to many relationships: one member can have many payments) - although possible in Excel, it will become complex and unwieldy, even following good practice guidelines and procedures.

If you're recording medical details, how secure does the system need to be? Does access need to be restricted to certain people? Again, possible with Excel, but you're getting into the realms of Visual Basic programming (although that may be the case in Access as well).

The medical details on their own won't necessarily be a problem in a spreadsheet, if you're happy for them to be free-typed into a field; if they need to be searchable or reportable (e.g. how many members have asthma?), that may be a different matter.

I would agree with WMittens about a database being more appropriate. But if that doesn't work for the users you've got, then could you use some database techniques? Maybe assign each person a unique membership number on the main worksheet, and then use this membership number on each of the group sheets to pull in the changeable central data with VLOOKUP?

Yes it's all depending in the relationships. Does each person only have 1 thing within each field, or could they have multiple. If the latter, and I think it is, you need multiple sheets or a database. And yes you would in effect see some of the data more than once, but it would only be editable once, on the master sheet. All the others would he linked protected cells. Gotta love data

You need to either create a relational database, or come up with rules to manage the information in spreadsheets.

Regarding that medical information - it becomes a Data Protection Act problem if held on computer and while the other lists of members are probably allowed as exceptions to data protection (there are a number of exempt categories and simple mailing list/ member list data is among them) the medical info is truly 'personal' data. In a way, holding sums paid/owed might be classed as personal data but there's no description info ("bad debt" comment) so probably avoids the need for registration.

I'd suggest keeping all the medical information on a card index and have it locked away, thus avoiding the need for/ cost of/ registration for the DPA, and the continuing fees to maintain that registration.

It also means you can keep the simpler collection of data together without having to worry about storing extra details just for this one membership list.

Network Guy

I thought DPA covered any filing system (electronic or paper-based) where information is easily recoverable - is that not the case?

You're correct, that is the case, if someone could find personal data just by looking at your paper based system alone.

If the card index held no name as a key, but used a reference number instead, with additions being given a unique and increasing reference number, that would not, of itself, be a 'relevant filing system' under the terms of the DPA.

It means that unless one was looking at the appropriate 'club' or 'group' spreadsheet where the individuals each had a unique reference number assigned, one could not identify whose record was being viewed if you chose one of the index cards at random.

(Yes, it is a bit concocted to avoid needing to register, but it also provides a degree of security so the card index alone would be no good to someone, while at the same time, the easy access of a spreadsheet could not be used to retrieve any medical information, as that would be held separately.)

I think I'm just explaining same ideas that others have said, but maybe will help a bit:

You don't need a single spreadsheet to incorporate all columns.

A master list of members with most basic details, such as postal & email address.

Other spreadsheets fill specific cell values by referring back to the master one.

You delete a record (row) in the master, it will turn into a missing-value in the others for name+address. Even if the child-spreadsheet still has data for other fields.

When you mail merge, sort by addressee perhaps, or look out for missing records. Either way you will spot the missing records. I think things like macros come into making sure it all updates automatically.