I found another Excel geek at Dreamforce

So this week I've been out in San Francisco at Dreamforce '07, and I've learned some incredible things about Salesforce.com -- better ways to extend what we're already doing and some cool new features do to hit this winter. I've also met some amazing people. One such person is Ezra Kenigsburg, a consultant and Salesforce.com Data Architect out of Chicago working in the Professional Services group.

Turns out Ezra is a bit of an Excel geek, too. Actually we met at a breakfast discussion of analytics, and he was mentioning that he was "...a bit of an Excel geek..." so I jumped in and said, "Oh, really? Well, I am THE Excel_Geek." We got along just fine from then.

Once we were finished chatting about Salesforce stuff, we jumped on this blog, so that he could see what I was all about. The last post on "Filling in the blanks..." was up, and a second or two after he read it, he showed me a far better, simpler way of doing was I had done.

Ever heard of "Edit --> Go To... --> Special"? Well, it's a pretty cool, little known feature. What you can do is select a large range, like we were dealing with in "Filling in the blanks..." and go to Edit --> Go To... --> Special, and select "Blanks". This will select only those cells within the range that are blank. (There are many other cool options, too, like selecting only formulas, comments, conditional formatting, data validation, etc. Oh the possibilities! Anyway...) Once you've got only the blank cell selected, if you click up into the Formula Bar and type in a formula you want -- in our "Filling in the blanks..." case, we wanted to average the four cells immediate left -- and instead of hitting <Enter> you'll hit <Ctrl> + <Enter>, which will copy that formula, adjusted for relative ranges, into all selected blank cells. Wow. The simplicity. Wanna make 'em all red now, simply Format --> Cells... --> etc.

Thank you, Ezra. Truly a pleasure to meet you.

Filling in the blanks - Interesting $50 Project

Recently I completed a $50 Project for a client who had what could be a fairly common data problem -- missing data points.

This client has large tables of data over a series of dates, but for some reason he's missing data points for some of the dates. To fill them in, his plan was to simply average the values of the four prior dates. In cases where the blanks occurred within the first four dates' data points, he was just going to put zeros.

The formulas needed to do this were simple, but there was a problem: His data table was very large. It would take him a long time to copy-paste that formula over and over.

"Isn't there a way to use to Excel's replace function to find blanks and replace them with these formulas?" my client asked. "Oh, and it'd be nice if when blanks were replaced, the font color was changed, so I can see which points are averages and which ones are authentic data values."

Here's kinda how my thought process went as I tried to solve this problem:

Hmmmm.

Well, the replace function idea wasn't really a go, because even though you can use it to replace blank cells with formulas (simply leave the "find what" field blank, put the formula -- e.g. "=1+4" -- in the "replace with" field and select the "match entire cell contents" option) there's no way to specify which cells to reference in the formulas. Plus the replace function, by itself, couldn't change the font color. You could use conditional formatting and change the font color if the cell contains a formula, but wait...there's no built in Excel formula to test if a cell contains a formula...and you can't just look to see if the leftmost character in the cell is a "=", since that evaluates the value in the cell (the result of the formula) not the text of the formula. I could have just wrote a custom function in VBA -- say, called ISFORMULA -- to evaluate whether or not the cell contains a formula. That VBA might have looked something like this:

Function ISFORMULA(CELL_REF as Range)

If Left(CEL_REF.Formula, 1) = "=" Then

     ISFORMULA = True

Else

     ISFORMULA = False

End If

End Function

But none of that mattered because, again, the replace function path wasn't going to work, so I instead wrote a relatively simple macro that would allow the user to select the range of cells he or she wanted to check for blanks and replace them with either zeros (if they were in the first four date columns) or formulas to average the prior four date columns' values. Then if the blank was replaced with a zero the font would be changed to green, and if the blank was replaced with the average formula, the font would be changed to red.

In the end, the result was a solution that didn't make any material changes to what the client was already doing, but solved his problem with the click of a button.

Insiders Subscribers, your file's on the way. I also made some explanatory notes in the spreadsheet in a comment and put lots of explanatory comments in the code itself.

Enjoy.

Excel_Geek

Oh, and did anyone notice two shiny new features (one temporary, one permanent) on the blog?

1. An image and link on the left side promoting a charity fundraiser my wife and I are involve with in the near future, and
2. A Google search feature on the right side. Now you can easily look for those oldie but goodie posts from way way back...