I'm not sure if Excel can do this, but hopefully someone can tell

[Guest]

I have created multiple defined lists for the user to pick. I want to be
able to calculate that based on selecting the combination of choices, it will
return a dollar value. I don't know if that makes sense to those of you
reading this, but here's a little more detail.

(Each column has a drop down list derived from another worksheet in the
workbook)
Column1 Column2 Column3 Column4 Value
Choice1 Choice1 Choice1 Choice1 *Need it return
a $*
Choice2 Choice2 Choice2 Choice2 ""
Choice3 Choice3 Choice3 Choice3 ""

Basically, I want to calculate the 4 text columns, that depending on the
combination of choices picked, it would return a specific value for that
combination. Does this make any sense. Someone please help!!

 

[Guest]

Yes that is possible to do in Excel, I guess it would mean some nested IF's,
AND's, OR's and SUM functions... Other way that I think about is maybe the
value picked returns a value with a VLOOKUP, INDEX, MATCH, etc function and
then the sum of those for or so? depends what specifically want to do...

 

[Guest]

Nothing like trying new things! I have to admit I am trying to push myself to
learn a little more. I havn't used VLOOKUP, INDEX or MATCH before. Any advise
to things to watch out for?

 

[Guest]

Renee,

Here is a little solution to your question that will get you started.
If you haven't used these before, I recommend you start with VLOOKUP.

Your first sheet would look like this, with the drop down selections
(data>validation>list) in columns A to C. Column D would bring them all
together to give you a unique identifier.

ColA ColB ColC ColD ColE
aa ee ii =A2&B2&C2 =VLOOKUP(D2,Sheet2!A:B,2,FALSE)
bb ff jj =A3&B3&C3 = VLOOKUP(D3,Sheet2!A:B,2,FALSE)

Sheet2 would look like this, for all the possible combinations from your
first sheet:

ColA ColB
aabbcc $29.34
bbffjj $12.64
aaccee $36.28
aaeeii $32.16

For the first row on the first sheet, the VLOOKUP will pull in the value for
aaeeii ($32.16, remember to do cells>format it as dollars). For the second
row it will pull in the value for bbffjj $12.64.

Hope that helps you get started.