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.
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 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.
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 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.