Excel 2000 - Referencing/Linking to a drop down list

[mdcr]

Is it possible to reference a drop-down list in a formula in Excel 2000? It doesn't seem to work the same way as referencing a regular cell because it doesn't have that same address scheme as a plain cell(C31, F53, etc.). The drop down list would look to have at least 4 values in it, but the number of values shouldn't really matter, as long as it can be referenced somehow... Thanks...

 

[GlennUK]

Can you explain further please. In what way exactly do you want to access a drop-down list in a formula?

Glenn.

 

[mdcr]

Say we put a drop down list in a spreadsheet, with the values of Always, Sometimes, Never, Maybe. If the respondent answers Always, then use 100% for the formula, if they answer Sometimes, then use 50% for the formula, and so on...Maybe a bad example, but we want to use a specific formula based on what value is selected in the drop down list...With a regular cell, you can say if C43 = x, then 100%, or if C43 = y, then 50%, but we don't know how to reference that drop down list because it doesn't have a regular cell address.

 

[GlennUK]

Can you explain what kind of drop-down this is? The drop-downs I use are always linked to a cell reference, or are actually dropdowns from using Data/Validation/List in which case the dropdown controls the actual entry in a cell.

Glenn.

 

[LeKirk]

I presume you have created a drop down-list from the control box tool pallete, in which case, there is a set of cells (specified by you when you set up the drop-down) a range that contains the words "Sometimes", "Always" etc., a cell that contains the value 1,2,3....n depending upon which of the words have been selected and how many words you specify and a cell that specifies how many words appear when the box is dropped down. What you do is use an IF statement. Lets assume you specify A1:A4 to contain your words, B1 to specifiy how many words appear in the drop-down at any one time and C1 to contain the value relating to the word position in the list. Your formula would now say something like: -
IF(C1=1,100%,C1=2,50%,C1=3,25%........)