If desired, you can use the optional TaggedFrog software to quickly browse through the documents. To achieve this you need to get the SkyDrive mounted on your drive N:
You don't need to do this or use this software, but it makes accessing the documents a lot easier.
Install TaggedFrog and then open the online spreadsheet:
There are a few extra sheets to manage keywords. The 'master' keyword sheet is called 'Keywords'. On this sheet you can add new keywords. When adding new keywords, a few rules have to be respected.
1) Keep the listed keywords in alphabetical order.
2) Column C needs to say 'All Synchronized' from row 2 onwards.
Add a new keyword by inserting a row in the alphabetically correct location, and make sure to copy-paste the formula of column C from any other row into the newly added row.
There are three 'Keyword matrix…' sheets. The idea is to have a single column per document, and for each document put a '*' in any row that corresponds to an appropriate keyword.
There are also a number of 'fake' keywords - each training session is assigned a 'fake' keyword SESSION-… (e.g. SESSION-ADM, SESSION-NETWORK01…). Put a '*' in a row that corresponds to a session in order to 'grab' a document for that session.
The 'Table Of Contents' sheet contains all of our documents, one per row. The 'Keyword matrix…' is flipped - it uses columns instead of rows to represent the documents.
Because Google Docs has a limit of 256 columns per sheet, I had to split up the 'Keyword matrix…' into three 'subsheets', each with room for 200 documents in 200 columns (column B - GS; GS is the 201st column).
Each of these subsheets contains a copy of the list of keywords in column A.
In order to allow flexibility in editing, the keyword columns on the four keyword-related sheets are NOT linked through spreadsheet-formulas. This is 'by design'.
That means you can muck around on any of the 'Keyword matrix…' sheets without fear of breaking formula-links. You are allowed re-sort the rows. If you accidentally inserted a row in the wrong spot, that can easily be corrected by re-sorting the sheet (sort on column A, range A2:GS…).
The easiest is to click at the left side in row # 2, then shift-click in the bottom-most row with a keyword (e.g. row #102 or higher - depends on how many keywords there are), and then use Data - Sort.
If you add a new keyword to the sheet 'Keywords', that keyword will not automatically be added to any of the 'Keyword matrix…' sheets.
You need to add the same keyword three more times, on each of the 'Keyword matrix…' sheets, and re-sort these sheets in the same fashion.
If you make a mistake, it will be flagged on the 'Keywords' sheet in column C, which ascertains that all four keyword-related sheets are 'in sync'.
The 'marker' to use for keywords in the matrix is * (asterisk).
After editing away, you need to re-calculate column F on the 'Program' sheet and column C on the 'Table Of Contents' sheet. Column F lists the materials associated with each session. Column C contains a blob of text that can be understood by TaggedFrog.
To recalculate the columns after a keyword editing session, I have a macro. Currently this macro is not accessible to anyone else but me; I'll try to remove that restriction.
For now, e-mail kris at rorohiko.com after making changes, so I can run the macro for you.
Using the macro is easy: select Tools - Script Manager, click on the 'CALCULATE_KEYWORDS_LIST' script and click the Run button.
This same script recalculates the 'Background/References' (column F) on the 'Program' sheet based on the '*' markers in the rows with the fake 'SESSION-…' keywords.
Once the script has finished, you should copy column C (from row 2 till the end) and then paste it into a text editor (e.g. Notepad).
From Notepad save this text file to a file 'TaggedFrog.csv' on your desktop.
Then make sure you have the SkyDrive mounted as drive N:.
Launch TaggedFrog, and select File - Import…
Select the TaggedFrog.csv file you just created and select the 'Clear library before import' option. Click OK.
TaggedFrog will mumble a bit. After that you now can search through the document collection by selecting one or more tags.
Play around with it - it's very powerful, but not immediately intuitive, so it takes some getting used to.