Thread
:
How to calculate based on Validation LOV items?
View Single Post
#
4
Posted to microsoft.public.excel,microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
Dave Peterson
external usenet poster
Posts: 35,218
How to calculate based on Validation LOV items?
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))
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
Reply With Quote
Dave Peterson
View Public Profile
Find all posts by Dave Peterson