Do your Excel formulas fill down far enough?

Special guest post today! Thanks Dave for sharing, and Ian for, um, volunteering. -- Jim

From: SD-Dave Brann
Sent: Monday, August 27, 2007 6:23 PM

How many times have you received this phone call?

Client: "My Excel reports are messed up."
You: "Ian must have messed them up."
Client: "Don't care. How can we fix them?"
You: "Send it to me and I'll figure out what's wrong. After I'm done flogging Ian."

And then after 15 or 150 minutes, depending or your skill, enthusiasm for flogging, and/or the complexity of the Excel workbook, you realize that the formulas on your "formatted" sheet don't go down far enough to cover all the rows on your "data" sheet.

Your first thought should be: Ian really should have used a Sheet Type of "TextFillReport", which would have taken care of this quite nicely.

But for argument's sake, let's say that's not an option - maybe this is an Inputs sheet issue. Or maybe you're dealing with a RMS issue (the "R" stands for "Restaurant" for any of you who aren't familiar with company history), where you don't have access to the wondrous capabilities of the modern Modeling Studio. What then?

Well, your second thought might be that we have VBA macros that try to help cover this case. But maybe you forgot to put them in. Or maybe you're not sure you remember what VBA stands for in the first place. Or maybe you think that your client would never, ever run 45 reps of your simulation model.

And so, we humbly submit the following suggestion:

Somewhere on your formatted sheet (or anywhere that you're confident the user will be looking), insert the following formula into a cell:

=IF(COUNTA('MyFormattedSheet'!A:A)<COUNTA('data(MyDataSheet)'!A:A),"Too Many Rows: Need to do a fill-down on MyFormattedSheet worksheet, columns A:S","")

To translate: count the non-blank cells in the specified columns of my formattted and data sheets (and yes, the COUNTA function counts formulas as non-blank), and if the formatted sheet has fewer than the data sheet, show the message. Preferably in a bright red, bold font. Otherwise, show an empty string.

Come to think of it, if you want to be really helpful with your message, you could tell them how many rows the formulas should cover:

=IF(COUNTA('MyFormattedSheet'!A:A)<COUNTA('data(MyDataSheet)'!A:A),"Too Many Rows: Need to do a fill-down to row " & COUNTA('data(MyDataSheet)'!A:A) & " on MyFormattedSheet worksheet, columns A:S","")

And that should put an end to those annoying client phone calls. (Feel free to flog Ian anyway.)

[Note: No Ians were harmed in the writing of this entry.]

David M. Brann
Senior Analyst
TranSystems | Automation Associates, Inc.