How to calculate based on Validation LOV items?

[rockfalls3]

Is there a function or VB script that could help with calculations
based on the items in
a Validation LOV? I'm looking for something similar to the "switch()"
and "case()" functionality found in C that will work in Excel 2003. In
"switch" and "case", the programmer takes a
parameter and checks its value, performing different operations based
on different values
(cases).

For my Excel example, I've got a Validation LOV consisting of multiple
values, let's say:
"Yes"
"No"
"Unsure"

I use this Validation LOV in a cell (A1), and want to calculate a
value in another cell (B1).

I currently use this function:
=IF(COUNTIF(A1:A1,"Yes"),10,IF(COUNTIF(A1:A1,"No"),0,5))
And so I get "10" if the LOV item chosen was "Yes", "5" if it was
"Unsure", and "0" for "No".
(Yes, I know I'm potentially running the COUNTIF twice on the same
single cell.)

Now the difficult part: if I add items to the Validation LOV, I now
have to nest additional "IF"
statements within the function/calculation, up to the 7-item limit, to
take care of those
possible values.

Is there any easier way to do this, where I don't have to do the
nesting but I can just cite
the position of an LOV item within the Validation list and perform a
calculation based on
that position or give that position's item a certain value? It might
work like this:

=LOV_CALC(<cell containing LOV>,<calculation(s) or value(s) to use per
LOV item(s)>)

I want something where I don't need to know ahead of time how many
values/items are in
the LOV, their names, or positions. Just something so I can either
assign a value for each
item on the list or perform some sort of calculation based on the
value retrieved (or its position).
I am not familiar with VLOOKUP, INDEX, or MATCH, so I don't know if
they cover this kind of
functionality.

advTHANKSance,

rockfalls3 "at" yahoo.com

 

[stjori]

Is there a function or VB script that could help with calculations
based on the items in
a Validation LOV? I'm looking for something similar to the "switch()"
and "case()" functionality found in C that will work in Excel 2003. In
"switch" and "case", the programmer takes a
parameter and checks its value, performing different operations based
on different values
(cases).

For my Excel example, I've got a Validation LOV consisting of multiple
values, let's say:
"Yes"
"No"
"Unsure"

I use this Validation LOV in a cell (A1), and want to calculate a
value in another cell (B1).

I currently use this function:
=IF(COUNTIF(A1:A1,"Yes"),10,IF(COUNTIF(A1:A1,"No"),0,5))
And so I get "10" if the LOV item chosen was "Yes", "5" if it was
"Unsure", and "0" for "No".
(Yes, I know I'm potentially running the COUNTIF twice on the same
single cell.)

Now the difficult part: if I add items to the Validation LOV, I now
have to nest additional "IF"
statements within the function/calculation, up to the 7-item limit, to
take care of those
possible values.

Is there any easier way to do this, where I don't have to do the
nesting but I can just cite
the position of an LOV item within the Validation list and perform a
calculation based on
that position or give that position's item a certain value? It might
work like this:

=LOV_CALC(<cell containing LOV>,<calculation(s) or value(s) to use per
LOV item(s)>)

I want something where I don't need to know ahead of time how many
values/items are in
the LOV, their names, or positions. Just something so I can either
assign a value for each
item on the list or perform some sort of calculation based on the
value retrieved (or its position).
I am not familiar with VLOOKUP, INDEX, or MATCH, so I don't know if
they cover this kind of
functionality.

advTHANKSance,

rockfalls3 "at" yahoo.com

In code terms you can use Select Case, e.g.
Select Case Range("A1")
Case "Yes" : Range("B1")=10
etc...
End Select

Btw your example formula could be simplified to
=IF(A1="Yes",10,IF(A1="No",0,5))

Could perhaps also look at the CHOOSE function.

 

[Guest]

With a table as below (say in Sheet2)

A B
"Yes" 10
"No" 0
"Unsure" 5

=VLOOKUP(A1,Sheet2!A:B,2,0) will return the corresponding value from
column B of the above table for value in A1.

Just extend table for Validation LOVs

Does this help?

 

[Dave Peterson]

I would use a different worksheet.

I'd put all my options for the Data|Validation list in column A (a1:a10, say).

Then put the corresponding values in B1:B10.

Then give that first column a nice name so I could use it in the Data|Validation
dialog.
See Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html#Name

And use a formula like:

=if(a1="","",vlookup(a1,sheet2!a:b,2,false))

 

[Dave Peterson]

Check your other post, too.

Is there a function or VB script that could help with calculations
based on the items in
a Validation LOV? I'm looking for something similar to the "switch()"
and "case()" functionality found in C that will work in Excel 2003. In
"switch" and "case", the programmer takes a
parameter and checks its value, performing different operations based
on different values
(cases).

For my Excel example, I've got a Validation LOV consisting of multiple
values, let's say:
"Yes"
"No"
"Unsure"

I use this Validation LOV in a cell (A1), and want to calculate a
value in another cell (B1).

I currently use this function:
=IF(COUNTIF(A1:A1,"Yes"),10,IF(COUNTIF(A1:A1,"No"),0,5))
And so I get "10" if the LOV item chosen was "Yes", "5" if it was
"Unsure", and "0" for "No".
(Yes, I know I'm potentially running the COUNTIF twice on the same
single cell.)

Now the difficult part: if I add items to the Validation LOV, I now
have to nest additional "IF"
statements within the function/calculation, up to the 7-item limit, to
take care of those
possible values.

Is there any easier way to do this, where I don't have to do the
nesting but I can just cite
the position of an LOV item within the Validation list and perform a
calculation based on
that position or give that position's item a certain value? It might
work like this:

=LOV_CALC(<cell containing LOV>,<calculation(s) or value(s) to use per
LOV item(s)>)

I want something where I don't need to know ahead of time how many
values/items are in
the LOV, their names, or positions. Just something so I can either
assign a value for each
item on the list or perform some sort of calculation based on the
value retrieved (or its position).
I am not familiar with VLOOKUP, INDEX, or MATCH, so I don't know if
they cover this kind of
functionality.

advTHANKSance,

rockfalls3 "at" yahoo.com

 

[Dave Peterson]

Sorry.

I didn't notice that your message was crossposted.