Dan -

 

A low-tech way to do it, though it doesn't include automatic updating, is to convert it into an Excel spreadsheet, insert a separate column for each of your groups, code the groups with a single letter at the top of each column, and spread each individual's participation choices across the columns by either entering an x or the actual letter designation of the group. Creating individual (or combined) rosters then requires only sorting for occurences in the selected column, along with sorting alphabetically by last name. Your asterisk designating audition requirements could appear in the heading of the applicable rows. And additional information could also be simultaneously sortable, such as voice part, specific instrument, skill level, etc.

 

It works best to have a single master database in which all updates are accomplished -- sorts for specific groups then come to the top when sorted, and everyone NOT in that group can be deleted for that particular partial roster. Be sure to rename before deleting.

Of course email lists, snailmail labels, phone lists, etc. can be easily merged for each group, or for everyone.

 

Name   Contact Information    A    B*    C    D    E    F    G   etc.

 

A = chancel choir

B* = concert choir

C = adult handbells

D = metrognomes (children's choir)

E = organists

F = directors

 

Doesn't do everything, but it is understandable by anyone who uses Excel.

 

Charles Q. Sullivan

cqsmusic(a)hotmail.com

Dan,

I've not been able to create a separate roster,nor have I tried, but I would organize the names (click on the top name of the column, if I remember correctly) so that all the sopranos, say, were one after the other on the list.

You still have the other members on the list, but all of the sopranos are together.  The color coding helps make the subgroups more recognizable. I used this method when I had two choirs rehearsing in two cities under two directors, who would be joining forces right before the concert (of Carmina Burana...but that's another story).  You will almost certainly have to go through and create separate columns for each group they belong to by hand, lumping all groups in column G is not going to serve you in the long or short run.

 

Not an ultimate solution, but it worked for me in that situation.

 

Lisa Mischke

Eugene, OR

Hi, Dan.  The computer guru for our annual summer musical tried using Google-Docs this summer, and had all the great arguments about how useful and helpful it would be.  Maybe it was, for HIM, but most people didn't even attempt to break through Google's very unfriendly interface, and that included our Musical Director.

 

I'm also not sure that it's a real spreadsheet--or maybe I just didn't have the right level of permissions.  I was orchestral manager, and I couldn't add and delete names and information they way I should have been able to--and the way I was told that I could.  Impossible to delete a row, for example.

 

Anyhow, after that experience, I would advise against using it, and stick with a REAL database, either a general one or a dedicated one.

 

All the best,

John

In Excel, you can set the columns to have filters on them (gives you a dropdown tool at the top of each column), you can then filter your column G to show only those entries that contain a certain value (using the Contains... option). You can then copy and paste just these entries into another spreadsheet to give you a list of the people in one particular group.

 

-- 

Steve

Within GoogleDocs, use the ImportRange Function.

Within Excel, use the Filter command (advanced, copy-to feature), combined with a macro for automation.

I have found google-docs to be a very good thing for making small groups more efficient.  Of course there is a learning curve!

--John

It is possible, but you need a somewhat complicated formula in a new sheet.

 

It would be something like:

=IF(G2 = "Chancel Choir", IMPORTRANGE("SPREADSHEETKEY", "MasterList"!A2:T2"))

 

Breakdown:

If the person in Row 2 is in chamber choir (because column G is their list of choirs) then copy the entire row to this sheet.

 

You would be able to drag it down so you wouldn't have to copy the formula every time. Just create a seperate sheet (or tab) for each ensemble.

Warning: This will make your workbook VERY slow, as it will be looking up 120 singers every five minutes to verify that the data hasn't changed.

 

Again, this is complicated and slow. Once you get the hang of it, it works fine. You'll need to understand how to use IMPORTRANGE, etc. My formula is just an example of where I'd start and tweak from there.

 

To the Google Docs naysayers, it is a most amazing tool for choir directors! The interface is simple and in a lot of cases it's much simpler than Excel. Yes, some of the features are complex, but you don't have to use them. If you can't edit/change things, someone set it so you couldn't. That's a human problem, not a Docs problem.

 

Now, if you wanted to complain about formatting for printing in the word processor, I'd be with you 100%. 

 

I'd encourage everyone to check out Google Docs, the power it gives us as choir directors for information management is incredible.

OK, here's a better way, using the Filter function combined with the Find function.  I made an example GoogleDoc that I think matches your scenario.  

Here's a screenshot:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Y'all are all MUCH smarter than me! Even Excel files scare me to death! :-)

 

Mary Jane Phillips

As has been mentioned already, the Filter function in Excel is by far the best way to go with this. Of course, that would require that if John Smith were singing in Chorus X, Y, and Z, he would need three separate rows with all his information (just the data in Column G would change). This is easy to do with the Copy/Insert Copied Cells commands. Just click on Column G so the entire column is highlighted, then click on Data/Filter/Auto Filter and you are done. Then the dropdown box that appears lets you choose which chorus to focus on (or you can create a custom filter that lets you choose multiple choruses). Everyone not in that chorus is hidden. I would recommend highlighting all the columns to filter every one just to increase your flexibility. Say you have voice part in Column F. Then you can filter Column G to see just Chorus Y, for example, then filter Column F to see just the tenors. Or you could see which members of Chorus X live in a certain neighborhood, if that is part of your data. You can get very creative with the data that way.

 

Good luck.

 

John Wright

I worked with Daniel to implement in his actual GoogleDoc the approach I outlined above in an example.  

 

Some of the nice features of a straight GoogleDoc approach (with no Excel intermediary) are: 

   1. Free software (of course you need internet access and a browser);

   2. Easy to provide secure access to the information to colleagues independent of their PC;

   3. The data is in only one place -- everyone sees the "current" version

   4. Eliminates or greatly reduces need for manual entry of data off of paper forms (the members directly enter their info through an online GoogleDoc form);

   5. As new entries are made, the break-out into the individual lists happens automagically;

   6. GoogleDoc viewers can set up email alerts to changes to the lists they care about.

   7. List maintenance duties can be easily shared amongst colleagues -- no one person is "it".

 

Some of the not so nice aspects of a straight GoogleDoc approach (from the perspective of an Excel user):

   1. Yet another computerish learning curve!  But there is very good documentation, etc., so no worse than learning any other nerdware;

   2. For tight security, all users must obtain google accounts, and login to their google account before gaining access to the GoogleDoc

   3. A less secure method provides access to anyone sent the link.  Given how emails can bounce around within a choir or church community, you should

       think twice about the kinds of questions you ask people to answer on the form.  Better to have a simple "ask Director to call me" check-box if you go

       with this access method.

Many thanks to all for your thoughts and suggestions, and especially to John Pearson, who went above and beyond to completely create the process for developing the sub-rosters.  They are finished and working smoothly.

 

A few follow-up thoughts on this process ...

 

* My only frustration at the moment is that I haven't yet grasped the formula-writing process well enough to recreate this solution on my own.  I hope to get up to speed on that some time soon, as this is a fairly typical application of a spreadsheet in our field.

 

* That said, once John P. wrote the formula I was able to paste it into the separate rosters.

 

* For those advocating an off-line Excel solution, I agree that - from my amateur perspective - the Excel interface and help tools make these processes easier to decipher, but as John pointed out, that does not address the universality, access and automation  of sending out a form by email that takes 30 seconds to complete that populates the rosters for me.

 

* Our music participants do NOT need a Google account to fill out the form.  If you would like to try out the form, please send me an email and I'll send it to you - if you want to try to fill it out, just put some kind of answer in there that lets me know it's a test, and I can delete it from my data.  Registrants never see the actual spreadsheet (our directors are able to, of course) so there is none of the intimidation one might expect.

 

* While I had to design the form that was sent out, it is incredibly easy to do.  It looks nothing like a spreadsheet, is intuitive, no esoteric knowledge is required, and it has very nice visual templates built in so that your collection of information feels friendly and fun.

 

* I realize that many would write this process off because of the seeming complexity of developing MY particular solution. Please don't let it stop you from trying it!  I could have designed a separate form for each group which would have eliminated this process, or might have been able to design the original form to populate the data more clearly, but that conversation is too long for this venue. 

 

* Our directors are not even aware that all of this has taken place.  They have been reluctant to pursue some of my automation schemes in the past because they seemed too "techie", but this one has been smooth and simple for them.  My children's music colleague had her first rehearsal last night and by her own initiative had two laptops (connected to the church wireless network) set up for on site registration.  The link to register is on the church home page (and I dragged it to the desktop) so as long as she could find the church home page she could get a fresh registration form.  All of these walk-ins are now safely registered on the master data base and the group roster, and no one had to touch pen or paper.

 

* I couldn't *begin* to make this happen using Excel.  (I realize that someone with advanced we development skills COULD) I WILL, however, print final paper rosters by downloading to Excel files.  I can customize those more easily and make them look just right.  But I will NOT maintain the data in Excel. It is a nightmare to have many versions of data and not know which one is current and correct, and on whose computer the roster resides, or having to log in to the church network by some complicated process to edit or access information.

 

* I'd love to have an informal lunch or something at the ACDA National Conference in Chicago where we could share GoogleDocs use for our work!  Anyone interested?

 

Thanks again,

 

Dan Wagner

 

Hi, Dan.  I'm late replying because I was unable to get a Reply window, but I seem to have it back now.

 

I think that all the wonderful advice you've been getting just goes to prove that if you are highly computer literate, you can get programs to do things they were not reallly designed to do.  That seems to be especially true of a program like Excel, which is a spreadsheet and not a database program, but which can apparently be forced to pretend that it's a database.  But I, for one, could never do all the workarounds that have been suggested or come up with all the fancy formulas that make things happen.  I would say that you're MUCH better off to start from the beginning with an actual database program that's designed to act like one!

 

All the best,

John