Excel-Fu: Selection Boxes and Cost

I thought it might be handy to post some of the tips and tricks I've been learning lately in various software programs. Because in order to teach myself I had to go scouring the internet for different sources, and maybe someone else is trying to do the same thing (plus the best way to remember something seems to be to teach it to somebody else, or if that fails write it down in the indelible ink of the internet).

Say you're considering building an assembly and have different options for different parts that would result in total cost differences. For my example I'm only using two components, but if you had a lot you might want something more automated. In the end what I needed was several drop down boxes where each option would reflect its cost and then add to a total below.

So here's how you'd do that which should be helpful if you've been trying to implement drop downs in Excel, use a lookup table, or figure out how to pull and use data from a selected drop down value.

In order to use a drop down you need what Excel considers developers tools. In order to turn this tab in, you go to File-> Excel Options. There, depending on your version of Microsoft Office, it might be under the popular tab, or might be under the "customize ribbon" tab. You want to have the "show developer tab" checked.

Then you go into your developer tab, click "insert" and there should be a drop down of various options. The one I'm using is the Form Controls -> Combo Box. But I imagine a lot of this stuff would work on either types of controls and boxes.

This will turn your mouse into a little plus sign. Go ahead and drag how big you want your box over wherever you want it. Then right click it and select Format Control.

But let's backtrack. First you're going to need some data for all this. I have two sets of options for my two widgets. You can locate this in another sheet of your excel document, but here I've stuck mine on the same sheet so it's clear what I'm doing.

Now back to your format control. It gives you three options to fill in in the "control" tab. Input range, cell link, and drop down lines. If I'm building the box for my first widget (options A through C) my input range is I3:I5. You can type this in, or just put your mouse in the box then drag your mouse over the cells you want. For right now, you don't care about the cost, only your "names". Cell link is the actual cell where your drop down value is going to be stored. I usually select the cell that seems most inhabited by my drop down box. Drop down lines is the number of values I have, 3 in this case, and I usually select 3-D shading cause why not, it's pretty.

Now you've got a drop down box that has some values in it that you can select. If it's bugging out at this point, you've probably messed something up in switching between sheets, so re-check your values and make sure they're still calling out the cells you want.

Next you'll need two columns to the right. One I've hidden because its sole purpose is to grab the "name" from the drop down. The problem with excel and drop down menus is it just correlates values to numbers. If you try to copy the value of your drop down it will just be a number of 1 through N where N is the number of options you have. In order to get that number to correspond to the value, you have to use a kind of silly workaround formula, the OFFSET formula.

In this case it's =OFFSET(the cell directly above my "names" table,the cell that my drop down box is storing its info in,0). The 0 is the actual offset value. Or in my case, =OFFSET(I2,C3,0). This is all important too because it means you need a row above your names table as you can see I had above. Now no matter which value you select in drop down, the column should mirror that value. Since you don't really need this column again later you can hide it (right click the whole column, select hide).

Next to callout the price value of your component you'll use the tables I showed a few figures up. This is the next colum over and uses the VLOOKUP function.

So the values I want here are =VLOOKUP(the value I'm converting, my WHOLE lookup table, 2, FALSE) or in my case =VLOOKUP(F3,I3:J5,2,FALSE). The 2 is because the value I want is in the 2nd column. If you were selecting a larger table you could specify any column you want. The F3 cell is where I used offset to grab the "name" value from the drop down. The false is to indicate I want only an exact match (for the name) rather than a partial match.

So now you can have drop downs with selected values, connect them to prices, and of course you can use a sum value (=SUM(G3:G7) ) to nab your costs and make a total further down.

Some of these methods are probably cheap workarounds, but still I hope you learned something. If you didn't and were bored sorry. Maybe you can write in with your badass software tricks.