Campbellisms - Comments from a 4-Year Old

Cyndi and I have been blessed with a terriffic little girl, who we named Campbell.  I’m sure that she’s just as amazing to us as most children are to their parents, but since I have a forum in which to write I figured that I’d post some of the comments that she makes.  Most of which just make us go: “WOW!”

I’ll add more and see if I can get them in chronologial order if I get the chance, but for now it’s more for her grandparents and godfather to see…I’d love to see other comments that kids can come up with, so please feel free to contribute!

• 1/28/09 - In front of her grandparents, when we were discussing animals:

“You know, squirrels are really nice,  unless you try to grab their nuts, or take them…”

• 12/21/09 - A rhetorical question I suppose:

“Dad, feeding a pig bacon is really, really wrong.  I mean, what is bacon made out of anyway?”

• 12/04/09 - Overheard while Campbell & Cyndi were playing a game:

“Mom, you’re really freakin’ me out…”

She’s 5…

When I have time I’ll recount Campbell’s argument for gum.  That was a fun one from a true soon to be sales professional!

08/01/09 - Or somewhere therabouts…

I have been plagued by a grand chicken experiment.  Cyndi decided that it was vital to our existence to support a chicken population.  This ongoing saga has seen us through about 40+ chickens in stages ranging from tiny chicks in a box with an incubator light to chickens in the hen house, with us (Cyndi and Campbell anxiously awaiting eggs)…So were the neighbors’ dogs…

My bane was the inevitable rooster that would appear.  Fortunately for me the dogs took care of most of them, except for one: Lucky (who wasn’t).  If you’ve ever had a rooster strutting around at 3 in the morning you’ll understand my position on the whole chicken thing.

I finally got the go-ahead to dispatch Lucky while the girls were out and did so.  Unfortunately, they drove up just as Lucky and his head separated and he went for his great last dash…Campbell thought the entire thing delightful and even wanted to help Mom prepare him for dinner. 

So now to it: the best comment I’ve ever heard regarding a chicken was from my daughter:

“Dad, what’s the best place for a rooster?

In the pot, COOKING!”

Gotta love that child!

5/6/09 - Campbell came up to me and was concerned about her first sleepover tomorrow, because her friend doesn’t clean up her room after playing in it; could I teach her to do it?  I told her that was up to her friend’s parents to teach her, but I could ask them both (the girls, not the parents) to clean up the room after playing in it.  Her reply to me was:

“You don’t have to make cleaning up a chore, Dad.

You can make it a learning experience. 

Like sorting by colors or size or shapes. 

Like put all of the brown bears in the toy chest.”

Mind you she’s 4, and when I reminded her of this an hour later and tried to employ her own logic she had a meltdown…

• We just happen to live in an area that’s made up of coastal mountains (coming from Colorado I call them hills) covered with beautiful, giant redwood and pine forests, in which the elusive hippies live.  I typically keep my hair short, because I predominantly ride a motorcycle and don’t use hair gel of any kind (Iiagine trying to clean that crap out of a helmet over time).  Mind you it’s not jarhead short, but when it gets long enough for helmet hair I get it cut.  But, I don’t need to know when to get it cut because Campbell let’s me know:

“Daddy, you’re a stinky hippie!”

• Playing in the Rain

We have a series of depressions alongside our driveway that turn in to a system of ponds/dams/streams when it rains and (despite being about 99% little girl) Capmbell loves playing in the rain.

One rainy afternoon, Cyndi and I were under our carport working on something as Campbell was stomping about in the mud boots her grandmother got her for Christmas, and Cyndi said: “Campbell, I think you missed one.”  To which she replied: “

Nope, got ‘em all Mom!”

Random comments that need no explanation:

• • “Mom, why don’t snakes have legs?”
  • “I like to wear socks to bed sometimes, they keep you from chewing on your toes..”
  • “Don’t be afraid, there’s nothing to worry about, you just need to be calm and it will be over in a minute…”  (As she came up with scissors and a roll of tape, trying to make a bridge between the couch and chair for a caterpillar she found in the yard).  She was much more reassuring than the nurse’s assistant who take my blood!)
  • “Dad, can you help me find my lizard?” (She caught three swifts (lizards) while playing in the yard and wanted to keep them.  Too bad she brought them in the house…)

Excel (and most other Office products) come bundled with what’s called Visual Basic for Applications (VBA).  It’s a subset of the powerful Visual Studio.NET programming language that Microsoft uses to write Office applications and they allow us to access/harness some of that power through VBA.  Most other product providers don’t let you do that, which is one of the reasons that Excel is so much more powerful than other applications.  For instance, Google Spreadsheets have no internal programming capability, so you can’t automate any tasks, while Lotus 1-2-3 only gives you the ability to record keystrokes.  In that sense you have more automation ability with Lotus than Google, but it is strictly limited to what you can do within the Lotus interface itself; you can’t get behind the scenes like you can with Excel, and it can make a huge difference to your daily work.

Case in Point: some time ago, when I first joined a business unit in San Diego, our assistant spent 8+ hours every Monday calling our satellite offices for their weekly sales numbers (by sales rep), which they then read to her over the phone and she entered into a weekly leaderboard report in Excel.  We had a computer order entry system, so realizing that the data had to exist in an electronic environment, I was able to set up a simple query from the system and then automatically populate a similar report in Excel using VBA.  What used to take 8 hours all of a sudden took about 8 minutes (including the time it took me to get a cup of coffee).  I was further able to automate tasks to the point where I could essentially take a day or two off each week because of how much time I saved at repetitive tasks (somehow I never did though).

I didn’t start out as a programmer, I’m not one now, and I don’t expect you to end up as one (unless you find it to be greatly beneficial to you, in which case you just might), but I will introduce you to how to start automatiing to your everyday work by recording macros.

NOTES: this article is written for Excel 2007 and its Ribbon Interface. The examples you see were created in Excel 2007. To follow the steps you would take in Excel to recreate the steps in the article I use the “Goto this, then” symbol: –>. I.E. goto the Home Button–>Open, as in goto the Home button, then select the Open option. As I’m a keyboard kind of guy I’ll also let you know the keyboard shortcuts, which will be notated in this style /avv, which means hit the forward slash key (it’s interchangeable with ALT, so you have your choice), then the keys listed, in this case “a”, then “v”, then “v”.

The Macro Recorder (is your friend)

Excel (and most other Office Programs) have a tool called the Macro Recorder, and you’ll also want to know that it’s probably your biggest friend as you start the foray into trying to automate your daily life.  If you’re not familiar with it, here’s a homework assignment you can do right now and then come back (note that VBA procedures are generally undoable without preparation, so make sure you try any code, sample or recorded, on a TEST workbook): I want you to think of a fairly repetitve task you undertake and goto the Developer tab, then Record Macro (/lr).

When you first start recording a macro you’ll get the following dialog box:

Record Macro Dialog

I’ll get to the options you have here in a minute, so go ahead and hit OK for now. 

Now go ahead and start doing what it is that you normally do for this particular task.  When you’re done, hit the Stop Recording button (/lr).  Now you can go to another worksheet and if you’re still on the Developer tab hit the Macros button (/lrpm), which will bring up a list of all the public macros stored in your workbook (public means that you can see and call the macros from the Excel side of the world).  Select the one you just recorded (it will probably be called “Macro1″), then hit the Run button and see what happens.

Macro Dialog List

VBA will now repeat what you recorded on the other sheet.  Pretty cool huh?

I have to note that the Macro Recorder is just that, a recorder, so it literally records everything that you do.  If you make a mistake and undo it, that will be recorded and repeated when you run the macro again.  It will also record all of your screen navigation, like moving from sheet to sheet or even scrolling up, down, across, etc., so while it’s a great tool, it can also be pretty inefficient as you (and your end users will see that activity - there are ways to get around it, but that’s another topic).  On the upside, it will also record the correct file path/name of a workbook, query, external database that you might be trying to access, which you might (would) probably otherwise screw up on your own.  I use the recorder all the time (as do many MVP’s) for just this reason.  Remember though, that you’re just telling a computer to do what you tell it to do.  One of my favorite quotes from fellow MVP, Ken Puls, is: “I hate it when my computer does what I tell it to do vs. what I want it to do!”  Recorded macros lack lack the intuition that you can add yourself within VBA. 

So now you know a bit about the macro recorder, and frankly some people never move past it, as it’s enough for them and that’s fine.  In fact, I know many people who are Finance professionals who never get past this point and it’s not a problem (I also know quite a few “Excel Professionals” who have never even made it to this step!)

Now I’ll move onto the options that you have when you begin recording your Macro(s).

Record Macro Options

Going back to the Record Macro dialog there are four options you have:

Record Macro Dialog

1) Macro Name - You have the option to name the macro whatever you want, although you need to follow some naming conventions (no spaces or special characters).  You’ll know if you entered an invalid name as Excel will let you know and you’ll have to try again.  I generally try to give my macros a fairly intuitive name, like “FormatSheet”, because if you start compiling a bunch of macros how do you know which one to choose?  Believe me, I’ve been tricked by ambiguous macro names and had to rebuild a workbook because of it.  By default VBA will name your macro “Macro1″, and each macro you record after that will be named in succcession (Macro2, Macro3, etc .)

2) Shortcut Key - Next you have the option of assigning what’s called a Shortcut key, which will allow you to call a macro with a keyboard shortcut instead of initiating the macro dialog list (I’ll explain how to call your macro(s) without shortcut keys in a minute). 

Note that shortcut keys are activated with the CTRL key, so you don’t want to use reserved keys, like P, otherwise you’ll overrride your abilility to print with that shortcut (the same goes for C-Copy, V-Paste, X-Cut, and most importantly Z-Undo - You don’t want to disable that one!  And if you do overwrite native commands you have to give them back, neither Excel nor VBA will do it for you, so be careful!) 

With that in mind, you can also take advantage of the Shift key as an additonal step.  To do this when you’re ready to assign the shortcut, hold down the Shift key and that will create a Ctrl+Shift+Key sequence (this is generally the route that I choose so I avoid any mistakes).

3) Store macro in - What’s that?  You have a few options when you record a macro of where you want to put it (it’s one of those behind the scenes kind of things).  If you click the arrow button to the right you’ll see that you can put your recorded macro in “Personal Macro Workbook”, “New Workbook”, or “This Workbook”.  You’ll generally want to choose to the “This Workbook” option, which will, as it suggests, store your macro in the workbook you’re recording the macro.  The “Personal Macro Workbook” option allows you to make a macro available to any open workbook (but that’s a topic for another discussion), and the “New Workbook” option is something I’ve never really had to use, so I’d ignore it and just stick with the default (note that if you do select something other than “This Workbook” the recorder will stick to that selecection for any other macros that you record, so you’ll need to be mindful of that, and switch it back if you have changed it…I’ve had a lot of macros “disappear”, because I put them in the wrong place).

4) Description - This is where you can add comments to your macro, so that when you open the Macros button from the Developer tab it will tell you what the code does (or at least is supposed to do…) 

Here is an example of recording a macro and naming it yourself, assigning a Ctrl+Shift+Key combination, then adding comments:

Record Macro Dialog

5) TheFinal Step - Calling the Macro and putting it to use…Now, from the developer tab you can click the Macros button (/lpm), and you’ll get a dialog box that lists all of the macros that you have available:

Macro Dialog List - with Comments

Just so you have an idea  of what multiple selections look like, here’s what my options look like when I do it (I keep several macros in my Personal.xls):

Smitty's Macro List

Here you have several options:

1) Run - The most common command, which will Run your code. 

2) Step Into - This is for more advanced use and allows you to actually step into the code with the VBA environment for testing and debugging.  Go ahead and try it if you’re interested (ALT+Q will exit the VB Editor and take you back to Excel).  If you choose this option, the F5 key will execute your code immediately and F8 will let you run it one line at a time.

3) Edit - Ditto.  This will take you directly to your macro’s code.  The same holds true with F5/F8 here.

4) Create - I’m not sure why Microsoft left this option here, as I’ve never used it (or seen it enabled for that matter).

5) Delete - Does what it says, so be careful.  There’s no Recycle Bin from which to retrieve a deleted macro.

6) Options - This gives you the chance to add a shortcut key and comments if you didn’t choose to do so when you first recorded your macro.

Now you know all you need to know to start recording your own macros and begin automating some of what you do everyday.  Bear in mind that as I mentioned earlier that the recorder is just that and nothing more.  Once you get the hang of recording some macros, you’ll start to wonder if you can do other things that the recorder just can’t capture.  The good news is that you can with some practice.  The best thing you can do if you want to  continue past what the recorder is to start understanding the VBA code itself.  Anytime you want you can select Edit from the Macros List and take a look a the code.  Let’s say you record a macro that applies some formatting; when you look at the code you should be able to see what the code is doing, based on the steps you took when recording it.  It’ll look a lot like Greek at first, but you’ll get the hang of it pretty quickly.

In my next post, I’ll discuss how to step beyond the recorder and begin editing your own code, then how to even start writing your own, which is when things really start to get fun and a whole new world of automation opportunity begins.

Good luck and most of all have fun!

NOTES: this article is written for Excel 2007 and its Ribbon Interface. The examples you see were created in Excel 2007. To follow the steps you would take in Excel to recreate the steps in the article I use the “Goto this, then” symbol: –>. I.E. goto the Home Button–>Open, as in goto the Home button, then select the Open option. As I’m a keyboard kind of guy I’ll also let you know the keyboard shortcuts, which will be notated in this style /avv, which means hit the forward slash key (it’s interchangeable with ALT, so you have your choice), then the keys listed, in this case “a”, then “v”, then “v”.

Now, by default all cells on a worksheet are set to “Locked” status.  This means that if you were to apply worksheet protection, you wouldn’t be able to interact with any of the cells on the sheet.  So once you have your worksheet designed and set up the way that you want it you can select all of the cells with which you want users to interact.  (Note that you can select multiple, non-contiguous cells by using CTRL+Left-Click.  The caveat here is that if you accidentally select a cell that you don’t want to be in that series you’ll need to start all over again, as you can’t de-select a cell that’s been selected in this manner).

Once you have your cells selected, on the Ribbon you can goto Home–>Format–>Deactivate the “Lock Cell” button by clicking on it (/hol).  You can also select the Format Cells button (CTRL+1), which will bring up this dialog:

Format Cells Dialog

The Number tab is the default, so you’ll want to click on the Protection tab, where you’ll get this:

Format Cells - Protection Tab

As with the Ribbon option, “Locked” is the default, so you’ll want to uncheck it.  Now here you have an additional option called “Hidden”.  This is an additonal feature that, when a sheet is protected, will not show anything in the formula bar when a cell is selected, as opposed the formula itself.  This feature should be used on Locked, not Unlocked cells, that you don’t want users to access.  I’ve found it helpful in the past with distributed workbooks that might have complex formulas in them, as by hiding formulas you keep users who don’t understand them from getting distracted and wondering what the heck the formula is or does.  Conversely, if you want to share your brilliance with people, then by all means don’t hide your formulas (the downside is that if you’re not very good with formulas you let everyone know it…)

Protection Options/Applying Protection

Expanded  protection functionality has been available since Excel 2000, and it gives you a  lot of options that before could only be accomplished via VBA (Visual Basic for Applications) programming behind the scenes.

When you protect a worksheet you can goto Review–>Protect Sheet (/rps) and you’ll see the following dialog:

Cell Protection Options

By default the Protect Worskheet option is checked, and since you’re there to protect the sheet anyway, the option is pretty much irrelevant (in fact, if you uncheck it, you lose the ability to protect the sheet, so it’s more than irrelevant).  Next you have the option to add a password.  I generally add a password for distributed workbooks, but if I’m testing a worksheet or if I’ve protected one of my own worksheets, I don’t add one (it can be a pain to have to enter the password to unprotect, then enter it twice to reprotect it ad nauseum). 

• Now here’s a note on Excel’s security: it’s notoriously weak.  But then again, so are locks on doors, and they both serve somewhat the same function in that they’ll keep 99.9% of people honest.  If someone really wants to get past your password, or door/window lock, it can be done in about 2 seconds (but I won’t tell you how, neither will Microsoft).

You can now enter a password, which you’ll be prompted to enter again to confirm.  Press OK and your sheet is now protected.  Before you go any farther, we also need to talk about the protection options you have (maybe the password input box should be at the bottom of the dialog box, as it’s the last step?)

By default the Select locked/unlocked cells options are enabled.  In some cases you’ll want to prevent users from selecting locked cells, although I can’t really think of a good reason for not selecting unlocked cells.  Microsoft probably put that there just to give you the option, even though you’ll probably never us it.

Following are the additonal features that you can enable/disable by choice and what they mean:

• Format Cells/Columns/Rows: if you have users who like playing with their colors and fonts, then you can let them.  Although I generally don’t make this available to them in order to preserve the integrity of distributed workbooks.  I’ve seen too many users “do their own thing” with workbooks, and then you end up with a horrid mish-mash of workbooks that all essentially (maybe) do the same thing, but look different.  It’s not a good idea if you want to have a coordinated presentation, especially with customers.
• Insert Columns/Rows: another iffy option, as users can manipulate workbook integrity, but you may have some cases where users need to ability to add rows or columns in order to add data.
• Insert Hyperlinks: this one is good if you want users to be able to add e-mail addresses or links to websites, e.g. customer sites, internal sites, etc.
• Delete Rows/Columns: another iffy one here.  In a distributed workbook, you need to be pretty careful about what you let users delete, lest they delete functionality.  I’ve had too many users call me to say that “it stopped working” only to find out that they deleted a row(s) of dependent formulas.
• Sort: very handy, and probably one of the biggest reasons why Microsoft added the increased Protection options.  Prior to this the only way to sort on a protected sheet was via VBA code.
• Use AutoFilter: ditto.
• Use Pivot Table Reports:  unless you have some high-end users, this feature will probably be unnecessary.  And if you do have users savvy enough to be using Pivot Tables, you probably won’t want them to limit them with protection, unless it’s purely for data entry purposes to prevent accidents (which are all too common - I have had more than my share of experience accidentally deleting some essential cell(s) that drives a data series and had to redo my work).
• Edit Objects: probably not very necessary, unless you have a pretty object oriented worksheet/book (lots of buttons, logos, etc).
• Edit Scenarios: this goes back to using Pivot Tables.  Unless you’ve got some high-end users you probably won’t need to worry about enabling this one.  You’ll know if you do. 

The Next Step: Workbook Protection

Unfortunately, worksheet protection won’t protect you from users deleting your well set up and protected worksheet, but workbook protection will.  No matter how much work you put into an individual or multiple worksheet(s), you’re going to run into an instance where someone deletes the entire worksheet.  In some cases that’s not too bad as you can just send them a new workbook, but sometimes, especially in collaborative workbooks, it can mean a lot of work to recreate what was lost, so you’re better off protecting yourself in the first place. 

Workbook protection is easy to enable by going to Review–>Protect Workbook (/tpw), where you’ll see the following options: 

Protecting a Workbook

 You’ll want to check the Protect Structure and Windows option, after which you’ll get the following dialog box:

Workbook Protection Options

Protecting for Structure: this is the default option, and it will primarily keep users from unhiding hidden sheets (like those that may contain Data Validation lists), actually hiding, moving or renaming worksheets, inserting new ones, or copying sheets to a new workbook (a note to this is that there’s nothing to keep users from e-mailing or copying the entire workbook itself).  There are some other options that will be disabled, but they are more for high-end users.  If you’re going to take this step, then I would also add a password, otherwise your workbook is essentially just a closed door with the door unlocked. 

Protecting for Windows: is not something I’ve ever found a need to do, but it will keep users from closing, or changing the posisiton or size of a window when it opens.   Again, use a password (and remember it!)

 

Restrict Permissions: I doubt that this is someting that you’ll need to worry about unless you’re dealing with very sensitive data, in which case, Excel isn’t the distribution product for you becasue of security reasons, as noted previoulsly.  Excel’s Permission Management  deals with Microsoft’s Information Rights Management Service, which is essentially a certification program that authenticates workbooks on your end if you’re a receiver, and on the other end if you’re a distributor.  Right now it’s on a trial basis, and I havent tried it, so I can’t really speak to it.  I know that Microsoft puts a lot of effort into security, and if it’s free, then by all means give it a shot and see how it works. 

File Protection Options 

There’s another step you can take with regard to protecting workbooks, although I rarely use it.  You can protect a workbook when it opens so that a user is asked for a password to open it, and also add a requirement if it’s Read Only (no changes can be made), or Read/Write (changes can be made).  To do this goto the Office button–>Save As–>Tools (/fa).  You’ll get the following dialog:

 

Save As General Options

Now choose General Options from the list and you’ll see this:

 

 

General Save As Options

 

The first option is to create a backup of the workbook, which will create a backup copy of the workbook in the same folder as the original workbook whenever it’s closed.   It will save with an .xlb file extension, so you can tell the difference, but in my experience this is an unnecessary feature given all of the great AutoRecovery work Microsoft has done and bundled with Office. 

 

Next you have the option to set a password to either open or modify the workbook.   This password is much more secure than worksheet protection, but it too can be broken (again, not an issue for 99.9% of users, but something you should keep in mind if you have sneaky users). 

 

Finally, you have the option to set the workbook to Read-Only, which means that users can view the workbook, but not make any changes to it.  If you’re going to be distributing reports for purely informational purposes, this may seem like a good idea, but you need to be aware that all of your formulas and hidden sheets will be included in the workbook, not only making them vulnerable to copying, but it also means that your file size is will not be compacted when you distribute it.  This can cause problems for slow e-mail systems, like Lotus Notes, where large files can cause problems with regards to on-time delivery.  If you are going to be distributing a workbook for such purposes, I’d highly recommend saving it in a PDF format, which Microsoft has included in the Excel 2007 Save As options.  It will create a condensed image file of your workbook that’s much easier to distribute.  If you need to share the workbook with other users, so they can interact with it and send you changes, then just go ahead and save/send as usual.

 

That’s about it for worksheet/workbook protection (other than what you can do with VBA, but I’m not there yet).  If there’s anything you think I forgot in this post, or if you have any questions, please don’t hesitate to ask.

 

Smitty

 

 

I was in a few recent discussions about how to deal with telemarketers (naturally I was amused having worked for a company that has telecenters), and stumbled upon a common theme: waste their time!  You know all those spam scams you get in your e-mail, like the famous Nigerian prince who wants to give you the contents of the country’s coffers?  I received one today that was actually pretty well written so I decided to respond to it (on a Hotmail account of course). 

—–Original Message—–
From: Barrister John Williams [mailto:barrjohn@earthlink.net]
Sent: Saturday, April 25, 2009 2:26 AM
Subject: Barrister John Williams

From The Desk Of Barrister John Williams

John & Associate Chambers

37 Sathorn Tai Road,

Bangkok Thailand

Email: barrjohnwilliams_bangkok@yahoo.com.hk

 Dear Sir/Madam,

Please kindly accept my apology for sending unsolicited mail to you I believe you are a highly respected personality considering the fact that I sourced your profile from a human resource profile database on your country. Though, I do not know to what extent you are familiar with events.

I am Barrister John Williams, a Solicitor. I am the Personal Attorney to Mr.Steve Anderson, who used to work with Siamrak Company Limited. On the 21st of April 2003, his wife and their three children were involved in a car accident along Sukhumvit Express Road.

Unfortunately, they all lost their lives in the event of the accident. Since then I have made several enquiries to your Embassy to locate any of my client’s relatives, this has also proved unsuccessful. After these several unsuccessful attempts, I decided to trace his relatives over the Internet to locate any member of his family but to no avail, hence, I contacted you to assist in repatriating the money left behind by my client in a Finance Company, Particularly, the Finance House where the deceased deposited the US$15 Million (Fifteen Million United States Dollars only).

Consequently, this Finance House issued me a notice to provide the Next of Kin to claim the US$15 Million (Fifteen Million United States Dollars only) in their custody within the next ten official working days. Since I have been unsuccessful in locating the relatives for over 4 years now, I seek your consent to present you as the next of kin of the deceased to claim the fund as the Next of Kin to him so that the fund will be transferred to your account by the Finance House.

Upon receipt of the fund, I will come over to your country to meet with you for the disbursement of the fund and then you and I will share the money in this order: 50% will be for me, 40% will be for you.While 10% is for execution of the fund incase of any expenses you encountered during the process of the fund into your account. I have all the necessary legal documents that can back our claim we will make with the Finance House.

All I require is your honest co-operation to enable us seeing this deal through. I guarantee you that this will be executed under a legitimate arrangement that will protect you from any breach of the law.

You are needed as a next of kin to inherit your brother left fund

Best Regards .

Barrister John Williams (Esq).

—–My Response—–

Oh my god!

Really, me?

Never in all my days here in Pahrump, Nevada, did I think such a thing could happen to me!

I would love to help you out, and Pahrump is lovely in July.  We even have an airport, but I doubt you can get a direct flight from Bangcock, maybe Tulsa, but not Bangcock.

Yew can stay with us if you want, provided yew don’t mind dogs (or kidz).

I’d be happy to let yew present me as next of kin, as mine’s all ded, that’s why the dogs.  They keep me compny.

I’m honest and hard werkin, that is, since I got laid off from the gypsum mine, it’s used to make drywall, they’ve got that in Bancock don’t they?  And I could use the money cuz of the mesotheliayomama I got down there.

Yew just let me know.  I’ll even have my cousing come pick you up at the airport, or we do have a Trailways bus stop.

If yew don’t want to stay at the trailer wif us, I can talk to Johnny down at the motel and he’ll fix yew up good with a rrom.

Looking forward to your visit, dumbass.

Of course, if this generates some interest, then you should feel free to respond to: Barrister John Williams [mailto:barrjohn@earthlink.net] personally.  I think he’d like that (whoever he may be)…Take care and Caveat Emptor goes both ways.

Every once in a while you stumble across some real gems on message boards/forums and I figured I’d start a collection of some of the ones I’ve run across.

Recently I was involved in a discussion where a poster wanted some help sorting some data, and was convinced that Excel 2007 doesn’t support sorting despite being given instruction on how to do it:

“Hi Smitty,
Excel 2007 does not have ascending or descending order.”

Ummm, yes it does:

“Yes it does. It’s just cleverly hidden. You can use the keyboard shortcut–> ALT+D+S+S or activate the Data tab, where you’ll find the sort options.”

Finally another poster put us out of our misery with this great response:

“You have two MVPs helping you, and other than turning your monitor upside down I doubt anyone can think of a quicker way.”

**********************
And a classic regarding protecting your code:

“Beware though, that a user with a middling knowledge of VBA will be able to get to your code. Personally I don’t have to worry, as my users are complete nincompoops…”

*************************

Term of the Day: SPREADSHITS

We’ve all seen some absolutely atrocious, poorly designed and less than functional spreadsheet that someone’s developed.  I often wonder why people who have no idea of how a good spreadsheet should be designed are given computers in the first place (let alone a driver’s license), but they’re out there, and can generally be found in almost any office.

Based on having to deal with a series of non-functional, poorly designed Excel workbooks distributed by none other than a division president (a Finance guy no less) and not having the ability to correct them let me to coin the appropro term “Spreadshit”.

If you can’t fix ‘em, at least you can make fun of  ‘em!

7 Surefire Ways to Guarantee Business Failure

(A Flippant Guide to Driving a Business Out of It)

1)       Revenue is #1

·         It’s over once the sale is made! That’s right, revenue is king and sales people and their managers are driven by sales VOLUME, not quality or service.  So SELL, SELL, SELL!  As I saw in one recent company e-mail: “We need the revenue, baby!  Don’t worry about customer service!  We can’t afford customer service without revenue!”  All you need to remember is: “You can sell ANYTHING once!”  When you master that simple concept you pretty much have the entire sales game captured.

2)       Customer Service is #2 (pardon the pun)

·         When you’re the big gorilla you don’t need to worry about customer service.  As long as REVENUE is pouring in and customers are coming to you there’s no need to worry about it.  Those problems tend to take care of themselves, and when customers go elsewhere you’re doing your competitors a favor by throwing them the scraps that keep them in business.

·         Never finish a job on the first day when you can easily finish it tomorrow!  Your guys will thank you for being able to go home early, and customers want to see that you’re thorough!  They don’t mind taking time out of their day, in fact it gets them out of work, so you’re doing them a favor by stalling!  You can even make yourself look like a star by not checking your orders and then explaining the extraordinary lengths you’ll need to go to fix the “supplier’s” mistake (even if it’s yours). 

·         Always give yourself room to hold someone else accountable.  In fact, that’s one of the first rules of sales!

·         Always make sure to take your personal life to work with you.  Think about how much better you can make your customers feel when they realize their life is so much better than yours!  Make sure to talk about personal issues (I find rashes to be a hit), how dissatisfied you are with your job and your company, and how Enron was so much better. 

·         Remember that the Better Business Bureau is irrelevant.  In fact they’re really hard to work with, so it’s fun to obfuscate who you are to make them work harder (after all, it’s your hard earned REVENUE that funds them).  First off, make sure that some random, powerless employee like Timmy (IT/CEO) is listed as the primary contact for all BBB documentation.  This makes it harder to get to you and gives you more time to generate REVENUE, rather than dealing with them.  Second, don’t answer customer complaints until you’ve been sufficiently lowered to a D or F rating.  When you get to that point, you’ve done your job, because you then don’t have to deal with those customers (who were probably deadbeats anyway).  No one likes to deal with dissatisfied customers, so have a “why bother” attitude, it really works!  This also serves as sales training, as it helps your sales reps learn to overcome objections.

3)       Communication is overrated!

·         Don’t answer the phone, but make customers go through your “automated” system, defaulting to Spanish, forcing them to press 1 for English just to show that they’re serious (and that they got the right number in the first place!)  Also note that you should keep it entertaining, like not changing your Christmas holiday notice until let’s say July.

·         At all costs, don’t let customers know when you’re coming for an appointment.  You should give them the ubiquitous 9-12/1-4 window like the cable guy, make sure you’re late and don’t give them a call to let them know that you’re on the way or won’t show up at all.  You have an entire industry reputation to protect (think of the millions of contractors and cable guys you’ll be putting in jeopardy if you don’t).  Plus, everyone loves a good surprise, so it’s all the better when you show up randomly!

·         Always make sure to let employees (especially top company staffers) use their personal gmail, hotmail, yahoo, etc. e-mail accounts for company business instead of a standard company address.  Seriously, how much more exciting would it be to get that corporate proposal for your nuclear reactor parts from spicyhotmama666@hotmail.com versus HildaRabinowitcz@AcmeNukeParts.com?  This has the added benefit of keeping Timmy busy because otherwise he’d probably be hitting on the cute sales reps, and that keeps them from generating REVENUE.

·         If you do use a standard company e-mail client, make sure to maintain the excitement by not enforcing a standardized naming convention (see Systems).  I once got locked out of a Fortune 500 e-mail system for several weeks (and had to use a gmail account), because there were two “csmith@company.com” in the system and they couldn’t figure out how to resolve it.  It was actually quite entertaining and led me to find some creative uses for my time, which is important; you want your employees to be creative (hence this article).

·         Management meetings are key to a successful business, so make sure that you have a lot of them.  Even better, make sure that no less than 50% of the people in the room only speak another random language (at random times with each other to add to the excitement).  I’ve found that Polish works well (Spanish is too passé).  You’re helping your managers learn another language and they can add “learned 17 Polish curse words” to their resumes.  Note that in said management meetings you should make sure to have lavish lunches, so you can take the scraps down to the production troops.  They like that. 

·         If you can ‘t get everyone together in one room, then make sure to use a conference call and remind everyone to not use the MUTE button, just so you can hear errant comments about you (how can you work on morale if you don’t know what “THEY’RE” saying?), and those lavish lunches being eaten.  The added benefit to conference calls is that you can add an element of surprise, so that most of the meeting attendees are in one conference room at the headquarters in LA, but the guys in Nome and Fargo have to call in and wonder what’s going on without them being there. 

·         Make sure not to use a real-time presentation application that everyone can see at once, like Windows LiveMeeting, WebEx, etc., but instead opt to send everyone a 95+ megabyte PowerPoint presentation (obviously not turned into an automatic PowerPoint slide show) and say “Now if everyone can move to slide 13…”, as you want to move forward.  Better yet, just so everyone can stay on track, be sure to use an entirely black background and have all executives print out copies of the presentation beforehand (it’s preferable if they also have inkjet printers - You need to keep your purchasing department busy buying ink and toner, and that keeps your office supply company in business – see how this is all connected?)

·         Everyone’s got a website.  So do yourself a favor and stand out by not having one (the Internet’s overrated and it’s run out anyway: http://www.endoftheinternet.com).  If you do decide to have a website, make sure your 14 year-old nephew creates you a GeoCities page.  It is critical to have him bury your contact information under so many layers that the only way you can find it is with Google (if you’re lucky).  You’ll pay way too much to have a reputable company do it for you, and no one knows your business like your nephew.

4)       Warranties & Contracts

·         We all know that warranties are just nifty sales tools that don’t really need to be honored.  How many times have you taken your warranty paperwork and just stuffed it in your junk drawer never to be seen again amidst the miscellaneous screws, double-sided tape and other crap?  See, you’ll never use it, so why plan on accruing revenue to account for a certain warranty refund percentage?  You’d be better off putting those reserves toward bonuses (yours). 

·         As for contracts, they’re really just ways to lock customers into your product or service (another nifty sales tool for your arsenal).  Once you’ve got ‘em never let a customer out of a contract, even if you can’t deliver the product.  (See #1 – It’s all about the REVENUE baby!)  And if you do let them out for whatever reason, make sure to short-rate ‘em.  Personally, I like the “but I’m going out of business” explanation; they all say that. 

·         You have to be tough on collections in order to give your contracts some teeth: an 82 year-old deaf widow can’t generate nearly enough negative press with “7 On Your Side” to justify not collecting the $782 dollars she owes for your service!  Hey, she’s got her husband’s pension and Social Security and you need to generate REVENUE (more important is that chargeback you want to avoid!) 

·         Now here’s a here’s a tried and true method for taking care of warranty/service calls:

“Bob’s Fly-by-Night Plumbing and Roofing…” 

“Hi, I’d like to call for warranty service.” 

“Can you give me your customer number?”

“It’s 1234567.”

“Yes, Mr. Truby, I see you bought your fulcrumfulminator from Bob’s Fly-by- Night Plumbing.  They’re out of business, so we can’t honor your warranty.”

“But your name’s the same!?”

“No it’s not, and we even have a new business license number to prove it.  Now, can I interest you in a new roof?  We have the best warranty on the market.”

5)       Don’t pay your bills on time

·         On-time payment is a silly notion; the concept of float is a fundamental business precept, and in this age of electronic payments, you need to try to keep that in play.  So by all means issue checks drawn on your Grand Cayman account(s), and if possible have your accountant (in Grand Cayman) issue and mail your checks for you.  That way you get a few extra weeks between the time the check’s mailed to when it’s received, deposited and (possibly) clears.  This should give you ample time to sell more (REVENUE) and move funds between your accounts, so you can balance your elaborate Ponzi scheme.  If you play your cards right Net-120+ is the new Net-30!

·         An alternative to paying your vendors is bankruptcy.  Just get your attorney (also in Grand Cayman) to file the paperwork for you and the next (after the appropriate filing period) day you can start over.  Granted, you’ll need to have someone else establish new vendor relationships for you, but that should be easy, because your “old” vendors are going to be anxious to make up the REVENUE that they just lost from YOU.  So it’s really OK, because you are keeping your vendors in business and giving their employees what they need (REVENUE = EMPLOYMENT).  It’s a joyous circle if you think about it.  Really.  It’s also a good opportunity for Timmy to get some diversification and career advancement, as you can tell him he can represent himself as CEO (he’ll really like the new business cards too…)

6)       Employees

·         Never hire employees who might have the skill or capacity to someday succeed you.  This way you can make sure your job is secure and simply blame them for lack of performance when you’re forced to let go of them.  But don’t feel bad, if you listen to your HR department (which is a MUST), even in an At-Will state, you can keep them around for months, if not years, with a series of meaningless performance action plans.  This also serves the added benefit of making you look busy, and therefore indispensable (it’s amazing how useless paperwork can validate your existence, so make sure to do as much as you can!)

·         For Sales or Executive positions make sure to rely on pre-employment tests like REID or MMPI to fully evaluate potential employees, and be sure that those tests are administered by qualified HR personnel.  HR has never been wrong in potential employee evaluations.  There has never been a good employee (especially in SALES) who failed one of those tests, and you need to rely on HR vs. your gut instinct.  They’re professionals, so don’t question their judgment. 

·         Nothing builds employee satisfaction and morale better than not paying your employees on time.  Better yet, be sure to bounce payroll checks and not issue timely replacements.  Consider it motivation at a time when so many feel entitled to a job and are looking for work.  Make them really work for it (not even unemployment comes on time).  And do not hire a payroll management service like ADP or offer direct deposit.  You want your employees to get out of the office more for their own well being (see Benefits below).  Going to the bank to deposit their checks does that and it also gives them time to interact with other bank patrons, who might need your service (REVENUE).  In addition, do you really want to give an outside firm access to your employees’ confidential information (Social Security #’s, Addresses, etc)?  I don’t think so, keeping it in house keeps them safe.

·         The same goes for benefits.  You can instill healthy living habits in employees by simply not offering healthcare benefits.  Those savings go immediately to the bottom line, and if you’re lucky, you’ll get a bonus for that new “employee first” initiative.  In addition, with the market as unstable as it is, you can make a good point for eliminating 401(k) and Employee Stock Purchase Programs.  Look at what happened to Enron employees; they lost their entire savings when Enron went down.  You’re saving your employees from potential financial ruin!  Plus, if you force them to exercise economic restraint at home think about how much more they’ll sell (REVENUE)!  Even better, just 1099 your “employees”, that way you empower them to take personal responsibility for paying their taxes and benefits. 

·         Mentoring and career pathing programs are huge (if you’re from Boston that’s “uuge”) in today’s business, so you’ll want to find the best “Management Mentor” from some highly accredited university, like Southwest Toledo State, and pay him/her at least  $200,000 over a 2-year period to develop a mentor program for your most promising executives.  Then in order for them to take full advantage of that knowledge, reorganize your organization, eliminating their career path and/or positions.  That way you force them to grow, just like you would want for a child, and you have the benefit of telling the board that you just saved untold millions in salary (and management consultants), and justify your bonus.  Plus this leaves you much closer to your customers, which is a benefit if you have an accounting background, because you can sell and collect at the same time.

7)       Internal Systems

·         Many well known shell companies are making a killing out of selling services and software, so always make sure to not outsource work that you can do a perfectly good job of screwing up in-house.  Ten years and $30 million dollars to develop a consolidated order entry system is probably a drop in the bucket compared to what Microsoft or IBM could do for you in a few months.  Plus, you need to keep those internal developers employed so you can demand MORE REVENUE from those hacks over in Sales!

·         Make sure that all internal applications are built by the same internal developers without regard to what end-users need.  After all they’re the experts, not you.  They know what you need, and you need to let them do their jobs and give it to you (sounds like HR doesn’t it?)  Believe me, when a systems guy says “it can’t do that”, they know, they’ve been out there and seen what you need.  You just need to live with it. 

·         Never, ever, put standard naming conventions in place for things like company e-mail or the master customer database(s).  It’s always a fun surprise for customers to receive a letter from your company addressed to “SMITH Chris ***THIS GUYS A JERK***”!  Giving your people the ability to enter customer information however they want helps them be creative, and it will keep your Marketing guys busy.  And we all know they need to be doing some real work instead of spending the REVENUE you’re trying so hard to bring in!  Think of how many ways you can make them earn a buck writing a masterfully crafted customer letter like this:

Dear Mr. Chris:

SMITH, we at Acme Nuke Parts value your business…

·         Electronic/Digital Documents: it’s so much easier to give your customers access to your paperwork and forms in a digital format, rather than having your sales rep lug them around.  So make sure you talk to Timmy (IT/CEO) and have him go out and spend $50K on a Sharepoint Server so you can host digital documents in a “document repository”.  But your next step is critical: you cannot at this point overwhelm your employees or customers with truly electronic forms, so have your secretary scan (on an old HP12cp5456 from 1985) all internal & external documents (including crumpled NCR forms), and put them up on your site.  Make sure not to upgrade any of your forms to include new company logos or have a consistent look to them; that’s key and it adds to the surprise.  All this talk about “unified” formats for stuff is overrated.

       Bonus Topic – Efficiency

·         Today’s workers are far too efficient, so don’t use industry standard applications like Outlook or Office.  Instead use Google Docs or Open Office and Lotus Notes to ensure that corporate documentation isn’t shareable with your customers and communication slows to a crawl.  After all, if you slow down your employees in such stressful times, they’ll be much more relaxed and be able to sell more (REVENUE)!  And customers will enjoy the challenge of trying to figure out how to access unknown file formats (actually you’ll be giving them time with their families as their kids, or the 14 year-old nephew who created their website, will have to help them with it, creating a stronger family dynamic).

       Bonus Topic #2 – This just in…

·         Whatever you do, make sure to lay off more salespeople, and then announce a rate increase for your most competitive products.  This will help with your  new “employee first” initiative, because you’ll make what’s left of your sales force cover more ground in a day (exercise) and it will make them better at handling objections.

This article is based on real-life experiences with Fortune 500 (and not so fortunate) companies.  All of the described scenarios are frightenly real, however real names used have been changed to protect the innocent and should not bear any resemblance, intended, or implied, to real people or companies (Timmy, your Mom said you want a new XBOX game for Christmas.  What do you want?  I think I can get HALO3).

(Why only 7 tips as opposed to 10?  Well, 7’s a good start isn’t it?  It’s a nice round number…And don’t forget the 2 bonus tips.  Plus I’m sure I’ll have enough fodder for more with your comments, to which I am looking forward!)

So I show up at my store early as usual, take my gear off (I ride a motorcyle to work) and start my day.

My sales reps trickle in and one says “Smitty, where’s the TV?” (a nice 42″ flatscreen we use to run loops of company stuff for customers).

I look, and sure as hell, it’s gone. I call some other store managers and see if anyone took it as a joke, but they all ask me if it’s an April Fool’s joke on them. I call our manufacturing guys and see if they did it, but nope, so I call the police. Just as a vendor rep shows up for some training with us.

I then have to interupt her as a criminal investigator shows up, then his boss, then a csi. They get busy asking all kinds of questions, taking pictures and dusting for finger prints…It seems that the back door had been jimmied.

Next the landlord comes by because he’d heard about the break in, and he asks me if I have his rent check. I don’t because that’s handled by accounting, but I make a few calls. He then says “Well you fellas owe two months, and I want certified funds. The last check I got had a stop payment put on it and the one before that bounced…” That made me feel just great in front of the police, who are asking me at that point if anyone would have any reason to be disgruntled with us (we’re in the process of restructing the company because of stuff like this, and have employees and vendors who either haven’t gotten paid or their checks bounce). At this point I’m feeling a whole lot like George Thorogood:

“Had to tell the landlady I’d-a lost my job
She said that don’t confront me,
Long as I get my money next Friday
Now next Friday come I didn’t get the rent,
And out the door I went..”

Then my boss shows up and the cops talk to him for a bit and they ask him the same questions. When they get to the disgruntled part, he laughs and says “We’re all disgruntled!” Smart boss, real smart…

The day goes along fine until about 5:30 when the boss calls from another office, where he’s with the owner. The good news? As part of our restructuring, we’re closing my showroom, to open in a much better location (it’s in a run-down shopping center now). The bad news? I have to lay off my sales reps immediately, and close the store.

One helluva day…

Data Validation is one of Excel’s most underutilized tools from a data entry perspective, especially its ability to manage list type functions.  What is Data Validation?  Well it’s essentially a robust set of tools that allow you to define the parameters you want for certain data entry cells.  In other words, you can control what users can & can’t enter in the cells that you define. 

How many times do you have to enter some of the same information in a form, or run into situations where users butcher what should be normal entries, or they don’t enter data in a format you need? Have you ever wondered if you could come up with a simple solution that would make not only your life easier, but your users’ as well? With Data Validation you can fix almost all of that (although no matter what you do you can always count on a certain population to completely mess up what you’re trying to do).

NOTES: this article is written for Excel 2007 and its Ribbon Interface. The examples you see were created in Excel 2007. To follow the steps you would take in Excel to recreate the steps in the article I use the “Goto this, then” symbol: –>. I.E. goto the Home Button–>Open, as in goto the Home button, then select the Open option. As I’m a keyboard kind of guy I’ll also let you know the keyboard shortcuts, which will be notated in this style /avv, which means hit the forward slash key (it’s interchangeable with ALT, so you have your choice), then the keys listed, in this case “a”, then “v”, then “v”.

The most common use of Data Validation is to populate a cell (or cells) with a list of pre-defined items.  Starting with a fairly common example, let’s say you have an expense report with a cell for users to enter their office location. Instead of relying on manual entry (and errors), you can use Data Validation to create a list of locations for them to select instead (Figure 1).  I like to add a “helper cell” either above or to the left of the data validation cell to point users to where they should be looking to enter data (in this case “Office Location”):

Setting up your list:

There are two methods of creating lists with Data Validation. The first is a manual method using the Data Validation dialog directly. This is preferable for smaller lists that won’t vary often, like “Yes/No/Maybe”, “Male/Female”, etc. To use this method, select the target cell (where you want the selection to be made), then goto the Data tab–>Data Validation (/avv). On the Settings tab, in the Allow box, select “List”, then in the Source box enter your values, separated by commas, but with no spaces in between. E.G. “Yes,No,Maybe” (Figure 2).

As you can see, this is fairly easy for a short list, but can become cumbersome for larger lists, especially as the nature of the source dialog box is to allow you to select a range on the worksheet, so if you try to use the Home, End or Arrow keys when you’re in the Source dialog box, all of a sudden you’ll find a cell range entered into your list items, and you’ll have to delete it in order for the list to be valid (Figure 2.1). This means that you have to manually select each item with the mouse if you want to replace it, or retype the list.

Note that the two check boxes you see (Ignore blank and In-cell dropdown) will be checked as their default positions, and in general when working with lists you won’t need or want to uncheck them.

Setting up Data Validation Lists

The second method for creating lists is to 1) enter the list in the same worksheet and refer to the range it occupies (Figure 3):

Or 2) the method I prefer is to use an unused worksheet (which can be hidden) and create a named range for the list. Enter your list on the sheet and goto the Formula tab–>Name–>Define (Figure 4):

Named range as a list source

Now you can go back to the Data Validation dialog and refer to the named range (Figure 5):

And you’ll get the same results as if you referred to a worksheet range (Figure 6):

Note: it’s not at all uncommon to forget the “=” when using named range references, but you’ll soon find out you forgot it when you select your data validated cell and your option is “MyList”. All you need to do is open the Data Validation dialog again and add the “=” to fix it.

The nice thing about using named ranges is that you can keep your lists away from your users, by adding a sheet for them, then hiding the sheet with goto the Home tab–>Cells–>Format–>Visibility–>Hide & Unhide). This is also a handy method if you need to maintain data sources hidden from users, like LOOKUP tables and other references.

User Input Message(s):

The second tab on the Data Validation Dialog is the Input Message, which will pop up a comment box on the target cell when it’s selected:

This is probably the easiest part of setting up Data Validation and you set it up through the Input Message tab:

There are two elements to the Input Message, the Title and the Message itself, both of which are pretty self explanatory.  Note that there’s an option to show the message when the Data Validated cell is selected, which is pretty useless, as you don’t have to enter anything in there in the first place.

Error Alert Message(s):

The third tab on the Data Validation Dialog is the Error Alert Message, which will pop up a message box anytime a user tries to enter their own value instead of one that you have defined.

The entry for this is very similar to the Input Message:

The same goes for the check box to enable the Error Alert message check box as with the Input Message.  If you don’t want to use those features, there’s not really a reason to check the box.

That’s pretty much it for the List feature in Data Validation, but I’ll be adding more stuff about Data Validation soon.  If there’s anything you think I forgot in this post, or if you have any questions, please don’t hesitate to ask.

Smitty

Whew. It was a long, hard trail to follow to finally get to the top of the bloggosphere, but (with good equipment) I made it safely.

Climbing at Yosemite

I’m looking forward to sharing my experience with Microsoft Excel, Climbing, Business Development and more, through the new Smitticisms blog.

The next several projects are Microsoft Office Online support articles, and the focus will be Microsoft Excel 2007 . Alternative solutions for MS Excel 2003 may soon be available, because I understand that some users have not had the opportunity (or desire) to experience the extensively overhauled new user interface.

Please feel free to ask questions and make suggestions for new articles, because I’m here to help!