If/then function with drop down lists

**semiresponsive** · 09-21-2011, 03:45 PM

I'm trying to create an interactive proposal tool for our sales team. The tool should be able to calculate the a cost based on several colums worth of information. On my current spreadsheet Column A is for Quantity, Column B for the equipment type (which is a drop down list - let's call the things widgets 1-5) and column C is where I'd like the fancy stuff to come in.

Can I ask excel to calculate an extended cost using that data? Basically, it breaks down to an if/then function, but I'm not sure excel with cooperate with a drop down list in the mix.

Suppose:

IF Widget 1, THEN Quantity*$3
IF Widget 2, THEN Quantity*$5
IF Widget 3, THEN Quantity*$7
IF any other widget, THEN Quantity*$4

My formula would have worked just fine if it weren't for the drop down box. They didn't teach combining if/then functions when I took my excel class years ago. Perhaps because you can't? If anyone knows how, I'd sure appreciate the tutorial!

ETA: I've attached a copy of the workbook as it exists now. Cell M13 contains the cleanest version of the formula I've tried:

=IF(C13=curtain, A13*70, IF(C13=overhead, C13*23, IF(C13=wireless, C13*85, C13*4)))

**JBeaucaire** · 09-21-2011, 06:19 PM

Your formula in M13 is telling Excel to look for named ranges called curtain, or wireless:

=IF(C13=curtain, A13*70, IF(C13=overhead, C13*23, IF(C13=wireless, C13*85, C13*4)))

...because you left out the quotes to indicate they are text strings.

=IF(C13="curtain", A13*70, IF(C13="overhead", C13*23, IF(C13="wireless", C13*85, C13*4)))

**JBeaucaire** · 09-21-2011, 08:11 PM

For fun, you could make the price thing easier to maintain, too. Since you're creating named ranges off to the right with the options, you could put the price there, too.

I've added a few to get you started so you can see how the pricing can be quickly spotted, easier than a bunch of IF formulas.

**semiresponsive** · 09-22-2011, 12:07 AM

JBeaucaire - that's exactly what I need that spreadsheet to do! And you're right - it's much easier this way than what I was trying to make it do. A bad habit - always trying to go about things the hard way!

However, I think I'm doing something wrong. I tried to duplicate what you did but I'm getting a reference error. Can you happen to see why?

**JBeaucaire** · 09-22-2011, 01:04 AM

You need to add the second named range MotionDetectorPrice Refers To: ='Proposal Tool'!$W$2:$X$4

**semiresponsive** · 09-22-2011, 09:09 AM

I'm not sure why I'm having such trouble with this...but I can't seem to get it to work. I did finally find that it was the validation I was doing incorrectly (or, at least *one* of the things I was doing incorrectly) but I'm not sure where the ='Proposal Tool'!$W$2:$X$4 is supposed to go to work this out. In the validation? Because when I put it in I get an error that tell me I can't use data from other workbooks or worksheets in my validation...but it's from the same worksheet. Where am I going wrong?

I think this is the first time something in excel has completely flummoxed me. It's not a comfortable feeling!

**JBeaucaire** · 09-22-2011, 01:14 PM

Look in the Insert>Name>Define list. You'll see I added a DoorContact named list and a DoorContactPrice named range. BOTH names are used to get the price to appear in that formula we added to column K.

So, when you added a named range MotionDetector as W2:W4, you needed to added another named range of MotionDetectorPrice as W2:X4. This is the VLOOKUP table that is used in that formula to retrieve the price.

Examine the two pairs of names I put in, and you continue with that process.