And The Geek Shall Inherit The Earth ...

For those of you who don't know me well, I used to be a computer programmer. As I used to say (boast), "I tell my computer what to do. Not vice versa." A nice little skill that I developed was macro creation, specifically for applications in the Microsoft Office suite.

If you're a writer, you probably face the same quandary I face: keeping track of your submissions. Who you queried, what their instructions are ("We respond to all queries" or "If you don't hear back, assume we're not interested."), and when to follow up. The problem grows exponentially if you're submitting different works to different agents and/or publishers.

So I developed a handy-dandy spreadsheet to keep track of my correspondence.

If you have no desire to learn about my system, go ahead and bow out now. But if you want to see my spreadsheet "in action," keep reading.

As background, this actually was based on an Access database that I developed a few years back for the same purpose. That one got a bit cumbersome, as I found that in some cases I needed a way to have a "many-to-one" and/or a "one-to-many" relationship (to use the geek terms) between a work and a correspondence with someone. To clarify, if I'm sending a query letter to an agent, that's one-to-one. But in many cases, a poetry publisher or magazine asks for five poems. So that's many works in one email. But also, if I'm querying an agent (as mentioned, one-to-one) and he or she writes back and says, "Send more," now I have one work, but many emails.

Clear as mud? I used to be a tech writer, so I hope I can still convey things like this.

So I came up with this spreadsheet. I'm sure this first image will be hard to see, so after it, I'll describe what's there.

(Please note, I use OpenOffice, which is an open-source -- meaning free -- app that works like the MS Office suite; Excel will look a little different, but functions basically the same way.)

This is the header row. 

The columns are Key (I'll explain later), Date, Follow Up Date, Response, Sent What?, Via, Email, To What?, Name, Who, Genre, Work, Details.

I tried to set it up so you can simply read it, left to right. So that would be (the column headers are in ALL CAPS)...

"On this 12/20/11 (DATE), I sent a query (SENT WHAT?) VIA email to an agent (TO WHAT?) at company NAME, and this person specifically (WHO). I sent my novella (GENRE) named Udopia (WORK). Addtional DETAILs are ...

So a real-world example would be:

And it reads, "On 12/15/11, I submitted, using an online form to an anthology named Best Fiction. The recipient was not named. I sent a short story, "Tarantulas On Leashes."

Now to explain a few things...

You'll notice there is a "Key" field. In the above example, for both lines, it is 46. This was done to solve that one-to-many problem that I referenced earlier. Every entry should have at least two lines with the same key. The date you sent it, and the date that they say you will get a response by. Bear in mind, as I mentioned above, some say "If you don't hear back in six weeks, assume no." Fine. I can still put a date that is six weeks out in the Follow Up Date field, and copy that comment into the Response field. The beauty of this key field is that if I am sending multiple works to a single entity, there is one line for each work, plus the follow-up line, all sharing the same key. As you can see here:

key 45 represents five works submitted to one anthology, plus there is the follow-up line.

If you use Excel a lot, this should be obvious, but you don't need to "do math" to get the follow-up date. The "Key 45" entity did not state a follow-up date, so I am assuming six weeks. Cell C14 is actually a formula which reads "=B45 + 42"  Seven days times six weeks = 42, naturally. Oh, and once I get a response, I delete the value from the "Follow Up Date" field, and put the date I heard back in the "Date" field. You can do whatever you want; you could leave it there. This just works for me.

You will notice that I have highlighted in red text to-do items. Likewise, if someone has said "no," I color the text gray, to fade it out.

The Email column contains a hyperlink to where the actual email lives on my hard drive. Having it here allows me to call it up at will. Please note that I use an email program (Thunderbird) which brings emails down to my PC, as opposed to something like GMail, which keeps them on Google's servers. Of course, since the field holds a hyperlink, you might be able to store the link to a webmail message. Hmmm.

Bear with me. There are only two things left to point out ...

 

In order to keep things clean, I use the "group" function of my spreadsheet. In the previous images, I intentionally omitted the left-most portion of the screen. It actually looks like this:

You will notice a little box, with a minus sign in it. What that says is that I have "grouped" rows 2 and 3, as well as rows 4 and 5.

Once 2 and 3 are grouped, I can click on the minus sign to collapse or hide row 3, like thus:

I just think it's neater to hide the details, once an entry is "closed."

Please note that there is one weird quirk about both Excel and the OpenOffice spreadsheet. Logically, I would expect that I need to highlight everything I want in a group, and then choose the "group" command. But the reality is, you highlight ALL BUT the first. So in the example immediately above, I highlighted only row 3. Going up a bit to my "Key 45" example, I highlighted rows 10 - 14 (but NOT row 9) before hitting the "group" button.

And finally, you can use filters (native to both Excel and the OpenOffice version) to create "mini-reports," if you will. Do you want to know to which agents you've sent a given work? Filter on the book's (or article's) name in column L, "Work." Do you want to know which works you've sent to a certain agent? Filter on his or her name in column J.

Had enough?

If you are a writer (or an agent) and would like a blank version, please leave a comment. I'd be more than happy to send you one. I'll even throw in free "tech support," though bear in mind, you get what you pay for.

Just kidding. 

PS: I do realize that I didn't mention macros after talking about them up top. This spreadsheet has no macros...yet!