Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
How to calculate based on Validation LOV items?
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Items Per Hour | Excel Discussion (Misc queries) | |||
Calculate items based on calendar year ending today | Excel Worksheet Functions | |||
How to calculate based on Validation LOV items? | Excel Worksheet Functions | |||
Data Validation: items in one list relate to items in another | Excel Programming | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) |