Friday, June 13, 2008

Look away, baby, look away

Did I already mention that my new job is sucking up all of my free time? Every last minute of it, I say. But only because I let it. I actually like it a lot, and I feel like I have a good handle on my immediate tasks. My new manager is also very good, so I'm happy.

One of the urgent tasks for me (in broad terms) is to get a bunch of people across the company to update a bunch of records in a database and add new ones. In a very short timeframe. (Now let's be real here, they had a long time to get this done, but of course, I've only been at work for a few weeks and the deadline is coming up fast.) So my very unglamorous role at this point is to "nag" people into doing it without completely getting on their nerves.

I started off pretty good, but it all went off the rails today. I like to strike a balance between casual and formal in my email notifications. (Somewhere between "hey dude, go look at our gnarly database when you get a chance" and "the overlords demand your immediate attention to this time-sensitive matter")

I even got an email today from someone I've never met saying how he appreciated my "nice" tone in the emails :) That actually made my day. And it was going to have to do, because I totally screwed up the rest of the day.

The problem actually started last night. Around 7pm, I was still working, and waiting till I had to meet Nathalie for a late dinner.

I had put together a fancy-schmancy spreadsheet of an export of our database listing entries that needed to be updated plus their owners. The owners part was a little challenging - I had to link up our records to another internal database and do a VLOOKUP formula in Excel to match them up.

Oh VLOOKUP, how I love and hate thee.

So I was pretty proud of my spreadsheet with all of its nice columns and this extra data brought in by our friend VLOOKUP, and I proceeded to send it out to about 100 people whose names were on the list, after sorting it by name for their convenience.

Mistake #1 - Sorting the spreadsheet apparently only sorted *part* of it, so the data in one row across got all mixed up.

So I send this thing out, and sure enough, because my coworkers are sharp people, someone emails me about an hour later saying how he thinks there's something wrong with the spreadsheet because it's showing entries he doesn't own.

By the time I see this email, I've had a nice cocktail, dinner, and it's 11pm and I'm ready to go to sleep. So I figure I'll tackle it in the morning, and secretly hoped that there wasn't really a mistake and that he was wrong.

So I got in really early today (after a short detour to downtown Seattle to get Top Pot donuts for some coworkers who helped me out on some urgent stuff).

I quickly found the issue in my spreadsheet and "fixed it". or so I thought, and re-sent it out to the folks listed on the new version. And figured "whew", crisis averted, since it was before 8am so most people weren't at work and hadn't seen the old or the new versions yet.

Mistake #2 - Same coworker from last night (I think this guy doesn't sleep!) emails me again to tell me his entries still aren't correct. When I check it's because VLOOKUP is actually now looking at the WRONG column for the lookup ID number, and pulling in a completely different (and WRONG) set of owners for these entries. Because I added a "check" column to my spreadsheet and forgot to change the original formula. And then I knew it was going to be a bad day, truly.

So of course, I start getting a whole flotilla of emails throughout the day about how this looks totally wrong (because it *is*). When I correct *that* mistake and send it out again, I think that I must be in the clear because who could screw up that many times on something so simple.

Mistake #3 - Now people are emailing me saying they can't actually see any of the data because the spreadsheet is looking for some external data. At this point I am ready to cry. Because I had another spreadsheet open while working on this, and pasted over some cells with formulas, it linked the two workbooks so when I send out one, people who don't have the other get crazy #REF errors. And again I look like a raging idiot.

So I fix this, and get the bright idea to put this on the network, so I can fix it and not have to send out an attachment to 100 people each time. I did that, sent out the link with more profuse apologies, and was notified by someone else that his data still didn't look right.

Mistake #4 - VLOOKUP has a parameter for whether you want the search to be approximate or exact. I guess there might be times where an approximate search might be ok, but really? And the value for approximate search is TRUE rather than false, which is totally counterintuitive. So I fixed it, and since the spreadsheet was out on the network, it was automatically updated, hopefully before anyone else saw issues with it.

At this point, I was pretty much done. I have sent out lots and lots of apologizing emails and my credibility with some folks is probably pretty low, which means I need to work back up to *not* being an idiot.

I left work before 5 today and came home for a big fat nap. Now I feel slightly better, though I've still got some emails to reply to in my work Inbox. (more apologies).

Lessons learned:

1. Triple check spreadsheet before sending it out - maybe even wait until the next morning to look at it with fresh eyes.
2. Post documents on a network share rather than sending attachments. Easier to fix up and smaller message size if you resend.
3. Be VERY careful with VLOOKUP. It will bite you if you're not careful.

Sigh. It can only get better at this point, right?

3 comments:

  1. I feel your pain. Excel has never been my forte...and thankfully I'm retired and don't have to think hard anymore. Good luck!

    ReplyDelete
  2. @foxaz, I appreciate your visit ;)

    I'm usually pretty good with Excel, but maybe today being Friday the 13th had something to do with it.

    ReplyDelete
  3. Sorry you had such a sucky day. Yeah, (no offense), but Excel is definitely not the easiest thing to use, especially when the lists and stuff become large. Requires too much checking and double-checking and triple-checking just to make sure SORT or FILTER didn't screw something up. You should see the monstrosities we have to work with :( Ah...I'm working this weekend, too. Can't wait for mat. leave to start.... -LL

    ReplyDelete

I love comments, so please leave me your thoughts. Thanks in advance!

sharing is nice

Related Posts with Thumbnails