Thoughts From The Cake Scraps

In the world of dashboard and report generation every little trick can help.  The trick might save you time, make something possible, or just look pretty sweet.  I think that any one of these is a great reason to employ an Excel trick, but I have to be honest; I am partial to the last one.

The reason that I love Excel tricks that look sweet is that often times if it looks sweet then it is also more likely to be used and appreciated by others.  On top of all of that it can be fun just seeing what Excel can do.  With all of that in mind, I give you my first post on Excel Tips and Tricks: Creating a Dynamic Data Validation Range.

The first tool that you will need is the “INDIRECT” formula.  The INDIRECT formula takes a text string and evaluates it as a range.  So, whereas normally a formula might say ‘=sheet1!A6’ and INDIRECT would look like ‘=INDIRECT(“sheet1!A6”)’.  The main difference is that the second formula is text and can be broken up into text segments and concanated together like this ‘=INDIRECT(“sheet1″&”!A”&6″)’ which is the key because then any component within the INDIRECT can be part text part cell references, calculations, formulas, etc.  Here’s and example of that ‘=INDIRECT(“sheet1″&”!A”&3*2)’ or if cell B1 had a value of 6 in it ‘=INDIRECT(“sheet1″&”!A”&B1)’.  All of these formulas would return the value that is in sheet1!A6.

Whew! Take a deep breath after that.  Perhaps take a second to play around with it because the next part relies on it.

The difficult part about using data validation in a cell is that the data validation range must be in the same sheet as the cell you are validating.  Unless you use a named range.  You cand define a named range in 2 ways.  The first is to select what you want and then in the box in the upper left corner of the sheet type a name.  The second is to use Insert > Name > Define (2003) or Formulas > Defined Names > Define Name >Define Name (2007)

Here’s the setup.  I am entering “List of Letters” in Sheet2!A1 with letters A – F in column A rows 2 – 7.  Select Sheet2!A1:A7 and type “Data” into that box in the upper left corner.  You should now have a named range called “Data”.

Then in cell Sheet1!A1 Use Data Validation and select List for the “Allow” drop box.  For the source type “=Data”.  You should now have a drop down in Sheet1!A1 that has all of the values from Sheet2!A1:A7.  Now you have applied Data Validation to a cell that pulls from a list on a separate sheet.  Nice.

Now for that extra piece of flare.  We need to redefine this named range.  For this go back into the Define Name – select the name and click edit; in 2007 use the “Name Manager”.

Now, we get to use the INDIRECT from before.  We will start off nice and simple.  While the current name should be defined as ‘=Sheet2!$A$1:$A$7’ we will just make that and INDIRECT so we get : ‘=INDIRECT(“Sheet2!$A$1:$A$7”)’.  Now we want to change this so that we know how far down to go.  Right now we go to A7, but what if things are added.  We would have to redefine the name every time.  Luckily, we can use the COUNTA formula to count the number of non-empty cells.  In this case COUNTA(Sheet2!A:A) should equal 7 (one for the heading and one for each letter).

Lets throw that into our named range definition in place of the last “7” so that we get: ‘=INDIRECT(“Sheet2!$A$1:$A$”&COUNTA(Sheet2!$A:$A))’.  Notice that we have to put in the quotation and ampersand right before the COUNTA because we are building a text string.  As a final touch lets say that we don’t want the heading the drop-down box in Sheet1!A1.  We have to change the row that the range starts on so we get : ‘=INDIRECT(“Sheet2!$A$2:$A$”&COUNTA(Sheet2!$A:$A))’.

As a final wrench, lets just say that the list on sheet2 is moved from A1:A7 to A:3:A9 with A1 and A2 blank.  Here we would just have to add something to the formula on the end to pad it for the blank rows and change the starting row.  In this case we would end up with ‘=INDIRECT(“Sheet2!$A$4:$A$”&COUNTA(Sheet2!$A:$A)+2)’

Let me know your thoughts on this post and if this worked for you!

This has been a Thought From The Cake Scraps.

An interesting thing happened to me the other day. I was having diner with a friend at a Mexican place and I had a craving for a quesadilla. I scoured the menu for quite a few minutes and came up with nothing.  I sat there thinking to myself “how can a Mexican place not have quesadillas on the menu?  Perhaps they don’t actually eat quesadillas in Mexico and this place is hardcore authentic.”

Now I have no idea if they actually eat quesadillas in Mexico or not.  Frankly, I am more interested in food that tastes good than if it is placed into the “correct”category.  That said it was still a let down.  I had settled on something else and was ready to order, but I made a comment to my friend about how bummed I was.

It took him about 10 seconds to locate them and point it out to me.  What, you might ask, was my problem?

I’m not sure if it is from reading too many blogs, going to too many websites, or just filtering out stuff just because but I missed the whole section.  After looking at it a bit closer I think I figured out why.  It was made to stick out.

That might sound a bit counter-intuitive at first, but its not.  I skipped it because it had a different background then the rest of the menu.  It was colored to stick out.  But for whatever reason my brain just skipped it.  Near as I can figure, it is just because if the menu was a webpage that is where the advertisement would have been.  Or at least it was blocked off and so my brain just read it as advertisement even if it was in the wrong spot to make sense.  I just ignored the whole section.  Keep in mind that this is while I am activly searching, trying to find something in the menu and I still skipped the whole thing.

For me it proves how cluttered the world is.  It proves how hard it is to stand out.  It proves that the game has changed.  What used to stand out and draw attention no longer works because people’s brains have been overstimulated with advertisements.  Do you even notice the product placement in moves/shows?  Sometimes it is painfully obvious and you can’t miss them (and then it just looks dumb) and the other times you miss them entirely.  Too much clutter.

Do you ignore clutter?

This has been a Thought From The Cake Scraps.

When looking at a web site process, such as registration, it doesn’t take much testing to figure out that the answer is always less.

What information should we collect?  Less.  What questions should we ask?  Less.  How many steps should there be in checkout?  Less.  Less.  Less.  You get the idea.  The reason is that each step or bit of information, or whatever is another obstacle that the visitor has to overcome to convert.  Why add things that actually prevent the visitor from taking the desired action?  Because you’re greedy.  That is the only answer.  If you do not have a guest checkout, you’re greedy.  If you force someone to put in their address when signing up for an account to access ‘premium website features’ you’re greedy.  You only need an address to ship.  Don’t be greedy.

I was looking over a Slim-Fast package and saw this box that lets you “customize your weight loss journey”.  It sounded interesting.  I’m not overly concerned about my current weight, but was curious anyway so when I had a chance I decided to check out the site.

This custom weight loss plan is on the homepage.  Like dead center on the homepage.  You can’t really miss the red “Register Now” button.

The Slim-Fast Homepage

At this point, for me personally, I was already a bit hesitant.  I’m thinking to myself, why are they calling it “register” and not “Get Your Plan Now” or at least something that doesn’t sound as impersonal as “register now”.

Anyway, because I was interested I click through anyway.  Point them.  But look at the ridiculous amount of information (click to enlarge) they demand from the visitor (in this case me):

So here I am, all interested is seeing what Slim-Fast can do for me and this is what they give me.  It is about the worst possible experience I could imagine.  But, I pressed on.  I really wanted to see what this “customized plan” was going to be.  So, like any person who has no interest in giving out tons of information for no reason, I filled in the stuff with a bunch of junk info.

Boy did they get me.  After I took the time to fill the whole long form out (albeit with bad info) they don’t even give me the plan.  They e-mail it to me.  Well, I didn’t put in an actual e-mail address so it when to some random person.  At that point I just lost interest.  I could not go on.

So with all of this in mind, what was Slim-Fast looking to gain with all of that information?  Would they mail me crap I didn’t want?  Just e-mail me the coupon.  What could they possible need all that information for?  Invasive marketing was my only thought and that is why I gave them bad information.  I don’t want to be invaded upon any more than I can help.  But the bigger miss here is what they lost.

Sure, they lost my interest but they lost something much more than that.  They lost my money.  Imagine, here is a customer all set to lose weight.  They not only want to lose weight, they want to lose it with Slim-Fast.  And beyond that, they not only want to lose it with Slim-Fast, but they want a diet plan from Slim-Fast that will – in all likelihood – contain a fair amount of Slim-Fast product.  From snack bars to meal bars to shake powder to a shake-in-a-can.  And then different flavors of all of those things.  This was a free pass for Slim-Fast to sell a lot of product to a customer that wanted to be sold to.

Instead of taking this easy money, instead of helping me toward my personal weight loss goal – and using Slim-Fast to do it, which I would certainly tell my friends about -, instead of any of that they got a visitor that was just pissed at them.  I wrote them a letter telling them how bad of a site they had and pointed out the huge opportunity they were missing out on.  After several weeks I have not got a response.  This is not how you build your brand.  I guess I shouldn’t be surprised.

Give information about your product freely.  Get a customer to engage with your product for free.  If you are a quality product they will be more than happy to give you the rest.

In fact, this is a perfect opportunity to quote the late founder of Lands’ End – Gary Comer (who was a billionaire – so yes, it works):

Take care of the customer.  Take care of the employees.  The rest will take care of itself.

Do you agree?

This has been a Thought From The Cake Scraps.

I was reading this interesting article over at CNN about how Cannon is urging its workers to “go home and multiply.”  The issue, it is hypothesized, is that a 12-hour workday is common.  This leads to people being too tired to ‘multiply’ and now Japan is sitting at a 1.34 birth rate.

The problem here is that this fails to look at the long term goal.  Or rather, fails to take a global perspective into account.  News flash: the world is not getting any bigger and we cannot continue to expand for forever.  We already have resource shortages in many parts of the world and solving those problems will only lead to more of the same problems unless the culture is changed along with the food and medical supply.  We don’t need to continue to increase the global population to be successful.  In fact, it is probably the opposite (for more information on this topic read Collapse: How Societies Choose To Fail Or Succeed).

Watch out or this same thing can happen to your website.  It is easy to focus on what is perceived as growth.  It is easy to test and then restructure things to optimize.  But what does that mean for you long term?  Are you changing your culture along with your site?

Here’s what I mean.  If you find out that you can make the most money by showing a certain product on your homepage, that’s great.  You make money right now; you are ‘growing’.  But what about your other products?  If you do not step back and look at the long term impacts of your decisions you will find your company so far down the rabbit hole it will take MAJOR resources to change the direction of your brand.  Just because something makes the most money now does not mean that it will make the most money from a “lifetime value of a customer” view.

Similarly, if a company only can focus on the growth rate (immediate or lifetime value) of a particular product or line the company will miss HUGE opportunities in other parts of the business.  Just as Japan could increase the incentive of immigration if they wanted more people, your company should be looking at all of your product lines and options for growth.

The failure to see other opportunities can be devastating to a company.  Once you become a single product company, you are in a very tough spot (unless you have also changed your culture to match i.e. you only make one product but everybody in the world knows it is the best of all similar product by competitors).  Know your resources.  Consider the larger picture and the long term goals of the organization.  And try not to put in too many 12 hour days.

Is your company or website growing the right way?

This has been a Thought From The Cake Scraps.