About Us

Dave Peterson

Sorry.

I didn't notice that your message was crossposted.

Dave Peterson wrote:

Check your other post, too.

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Calculate Items Per Hour	Karthik	Excel Discussion (Misc queries)	2	May 15th 09 04:40 AM
Calculate items based on calendar year ending today	Illogical Lucy	Excel Worksheet Functions	1	May 6th 09 10:14 PM
How to calculate based on Validation LOV items?	[email protected]	Excel Worksheet Functions	5	August 29th 07 08:15 PM
Data Validation: items in one list relate to items in another	Paul D. Simon	Excel Programming	1	August 4th 05 09:17 PM
named range, data validation: list non-selected items, and new added items	KR	Excel Discussion (Misc queries)	1	June 24th 05 05:21 AM

Menu

About Us