Spreadsheets are a wonderful GM's tool. Its weird that only late in life that I have begun trying to learn all I can about it. I guess its because the technical aspect of the game, the details, are one of the main things that help me in my way of playing stay in character and fully visualize the world/setting.
I finally learned to use Data Validation (Data>Validity, choose Cell Ranges). I also learned how to use the Name box, and understand how it works to simplify writing data ranges.
Data Validations > Cell Ranges, is an easier way to make a cell create a drop down list. You just make a table (like the MANY tables in RPG games), select the table and rename it in Name Box so that you won't be having to type in cells and that it will be static (with the $ signs needed to keep it that way) when you cut and paste.
Thanks to DC and Ken, I learned to use Index and Match functions to create Drop Down lists that can affect the other. Although I had help figuring it all out from The Document Foundation users mail archive regulars Adreas and Tom*
The difference between using data validation and Forms>List box is that Forms can be made to activate macros. I learned to make some basic macros through recording my actions and steps. I would rather learn to script, but I don't have any time for that given the state of business and my obligations and responsibilities.
Collaborative GM tools
With Zoho, Google Docs, Live Documents and many other free collaborative document tools, I haven't heard of any GM enclave that shares and collaborates with these tools to make more of them or make them better. If there is a group, I'd be glad to meet and join them.
In a GURPS forum thread about such, replies are about the need for combat tracking and calculations. Which is contrary to my experience, I need the calculators in making preparations and ad-hoc vistas and opponents if the players become too unpredictable.
I hope you can perform data validation in the cloud documentation programs. I have yet to check it out and my internet is quite buggy right now**. With google docs you can keep many versions of the same spreadsheet in a collaborative folder, then copy paste the best in a mutually agreed on generic or allow anyone to cut and paste one, since you can describe the spreadsheet and allow other gms to pick and choose then customize when needed.
The nice thing of learning more of spreadsheets, It is easily excused for work purposes... which truly it also is. We are making several automated processes through automating the documentation. Our business model is so flexible: a mix of products, services, sales, managing vendors, Biz continuity programs, and so many combinations and iterations it gets dizzying and hell for our Finance department. If there were as a "spreadsheet to rule them all" or some minor spreadsheet forms to help, it would save valuable time, possibly work days in a project. We are a BPO and IT operator, builder, trainer, incubator, leassor, and soon to be network (cisco and juniper) outsourced training and certification for various universities, and Telcom partner its hard to get it all straight.
Already showing one of our departments how to use macro recording did wonders (our cabling, electrical and security systems engineers, they have proactive methods that love organization). Next is if give training for our technical secretaries to use Data validation and Cell Ranges to make our pricing more modular and flexible, allowing us to bringing detailed proposals to the clients door in a days before the competition.
Using forms allow us to make only Parts of a Contract Editable, and speeds up negotiation. even with record changes, sometimes they intentionally cut and paste entire sections of the document and Change Tracking just gets screwed up.
My favorite use is giving some of our more difficult clients, those forced on us, a whole lot of math so they can't be too much of an @$$ at a negotiating table. Ever negotiate with someone who THINKS they know your business and super low-balls and promises BS. The BS this one client was dishing was giving us projects into unstable dictatorships of central africa where UN and International agencies rate as a risk to life of Filipino visitors (a country with no political clout). Hanging central africal like a carrot, dangling that possibility... disgusting lie and a potential for human tragedy.
*hope they eventually move to a forum format, I get 10-40 emails a day from them and Digest won't let me interact very well with the discussions.
**calling the admin of our bldg to get it fixed with our Internet provider later.