Smarter Followspot Tracking With spreadsheet magic

When coordinating followspots, I like to give my operators a choice between the tracking sheet I use and cue sheets that have just the cues for that individual spot. Many choose the latter because it is easier for them to read and provides more room for notes. This situation creates quite a bit of extra work because not only do I have to update my tracksheet, but I have to do the same updates over again for each cuesheet (or at least copy and paste the changes in). Not only is this more work, but it also leaves room for errors in the copy/paste process and inconsistency in paperwork.

While working on Children of Eden at Kent State Universiy, I came up with a system that drastically simplified the process so that I only had to update the tracking sheet, and the individual cue sheets would automatically be updated. I did this by using some rather simple formulas in the OpenOffice spreadsheet program (the open source equivalent to MS Excel. All methods described here will work in either OpenOffice or Excel).

Let's say that you have named your tracking sheet tab "track_sheet" and that you have set up your spreadsheet as follows:

Cue #Spot 1Spot 2Spot 3
            
            
so this makes as much sense as possible, here is a chart showing how the computer names the cells of the spreadsheet:
A1B1C1D1
A2B2C2D2
A3B3C3D3
A4B4C4D4

Since we have used the first row(A) for titles, the actual cues will begin with the second row(B). The formula for the cue number in the first row of cues for followspot one(which is on another sheet in the same workbook) would look like this:

=IF(ISBLANK(track_sheet.B2);"";track_sheet.A2)

now this may seem a little overwhelming at first, but it's really pretty simple. The equals sign tells the program that this is a formula, so that instad of showing exactly what is written, it shows the result of the formula. Basically what this formula is saying is that if cell b2 on the worksheet called "track_sheet" is blank then it should'nt show anything (""), otherwise it should show whatever is in track_sheet cell A2 (the first cue number in the column). This formula assures that only cue numbers relevant to followspot 1 (column B) are shown.

The second formula that is used is the one to show the actual cue description:

=IF(ISBLANK(track_sheet.B2);"";track_sheet.B2)

As you can see, this formula is nearly identical to the first, except that instead of showing the cue number(A2), it shows the cue description for followspot 1(B2).

Now all that is left to do for followspot 1 is to copy and paste the formula into each cell and update the row number that is used in each formula. Sound like more work than it's worth? Not if you know another simple trick. Highlight both cells containing the formulas (either Ctrl+click both of them, or click and drag do select them). You should see a small black box at the bottom right of the last cell in the selection. Click on that box, hold, and drag down until you have enough rows to cover all of your cues. This will copy both formulas into their respective cells and automatically increment the row number to corrospond with its position. Now whatever you modify in the main tracking sheet will automatically be updated in this cue sheet and all you have to do is print it.

To add a cue sheet for the next followspot you just need to follow the steps above, but you have to change the formula to match the column letter that each particular followspot uses. Just replace the "x" shown in the formulas with the column letter.

Cue #
=IF(ISBLANK(track_sheet.x2);"";track_sheet.A2)

Cue instruction
=IF(ISBLANK(track_sheet.x2);"";track_sheet.x2)

Now that I've thoroughly confused most of you, here is a link to an Excel file that you can use as a template. This system has saved me an enormous amount of work and I hope it can do the same for you, so feel free to use it and modify it to fit your needs. If you find a way to make it better, don't hesitate to drop me a line and let me know how and I'll update this article with your ideas.