43 Folders

Back to Work

Merlin’s weekly podcast with Dan Benjamin. We talk about creativity, independence, and making things you love.

Join us via RSS, iTunes, or at 5by5.tv.

”What’s 43 Folders?”
43Folders.com is Merlin Mann’s website about finding the time and attention to do your best creative work.

Open Thread: How are you using Excel?

Yesterday, I mentioned I'd been talking with someone who's looking at interesting things people are doing with Microsoft Excel. I talked to her again yesterday, and with her official okey-dokey, I'll virtually introduce Tralee Pearce (*waves*), a reporter from Toronto's Globe & Mail whom you might remember from a very swell article about the Hipster PDA.

So, by request -- and to help Tralee with fleshing out her fun-sounding article -- I hope you all will jump in here: What kind of cool, novel, and non-obvious stuff are you doing with Excel? What's the wildest, most obsessive, most nerdy thing you ever saw someone do with our favorite spreadsheet program?

Chris Westling's picture

Under the crushing oppression of...

Under the crushing oppression of a cruel managerial overlord, I was not permitted to use relational database software to track calls for my phone support team.. The company’s PICK OS system, she said, was good enough, and the IT team already spent gobs of time specially coding four extra fields, and now I wanted more?

Stubbornly, I vowed to beat them at their own game. I sat for many nights with my copy of Visual Basic for Office Applications and Excel Macros for VBA in my lap, and eventually built a multi-user call tracking system.

Each morning, one button click on the master spreadsheet would retrieve new data about trouble calls from the company text file, neatly chopping, reformatting and importing text and numbers.

Each team member had their own separate client-side workbook chock full of behind-the-scenes code, so that when they typed their initials into a dialog box, their sheet would query the master workbook for their calls, and update their worksheet, cell by cell. They now had the benefit of searchable records, updatable notes fields, and could change the status of each call as they worked on it. At the end of their shift, each team member would click a button and update the master sheet with new data.

As team leader, I’d automatically sort and colorcode all of the data on the master sheet according to calls completed, problems solved, etc, and email (unheard of!) detailed reports and charts that the company system couldn’t give me. The coup de grace was creating a Windows keystroke macro (within Excel VB) that would select the data that the PICK system required from the master sheet, and paint it into the dumb terminal screens, character by character, completing the circle. SUPER-geeky.

This was back when “yak shaving” was still a Ren & Stimpy joke, but I was vindicated when bosses would ask, “How did you run these reports?”. My manager fumed, but I eventually got the okay to convert everything over to a custom database. Even though it was the long way around, the end justified the means, and I learned a boatload of Office VBA. I still go to Excel when I have a difficult data translation task, or want to take data from one system and jam it sideways into another.

 
EXPLORE 43Folders THE GOOD STUFF

Popular
Today

Popular
Classics

An Oblique Strategy:
Honor thy error as a hidden intention


STAY IN THE LOOP:

Subscribe with Google Reader

Subscribe on Netvibes

Add to Technorati Favorites

Subscribe on Pageflakes

Add RSS feed

The Podcast Feed

Cranking

Merlin used to crank. He’s not cranking any more.

This is an essay about family, priorities, and Shakey’s Pizza, and it’s probably the best thing he’s written. »

Scared Shitless

Merlin’s scared. You’re scared. Everybody is scared.

This is the video of Merlin’s keynote at Webstock 2011. The one where he cried. You should watch it. »