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

On Aug 29, 7:34 pm, rockfal...@yahoo.com 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

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.

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?



"(E-Mail Removed)" 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
>
>

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


(E-Mail Removed) 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

--

Dave Peterson

Check your other post, too.

(E-Mail Removed) 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

--

Dave Peterson

Sorry.

I didn't notice that your message was crossposted.

Dave Peterson wrote:
>
> Check your other post, too.
>
> (E-Mail Removed) 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
>
> --
>
> Dave Peterson

--

Dave Peterson