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?



" wrote:

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