How to Create a Conditional List

[Ken Sills]

Based on a conditional, I want a cell to either contain a fixed valu
(and no list), or alternatively a list using the verification/lis
capability. I cannot figure out how to do this. Using th
verification list seems to preclude using the IF function.

Also, is there any way when using the verfication/list capability fo
the cell to dynamically update? If the underlying list changes, th
old value stays displayed until the user selects from the list.

Thanks for any advice!

Ken Sill

 

[Frank Kabel]

Hi
both is only possible with VBA (using an event procedure). Would this
be feasible for you?

 

[Debra Dalgleish]

If the condition is based on the contents of another cell, e.g. A1, you
could display either a list with the fixed value, or a full list of options.

Create a range named "Fixed" --
Type the fixed value in a cell
Select that cell
Click in the Name Box, at the left of the formula bar
Type: Fixed
then press the Enter key

Create a range with your percents, named "FullList" --
Type your options in a list in the workbook
Select those cells
Click in the Name Box, at the left of the formula bar
Type: FullList
then press the Enter key

Select cell B1
From the Allow dropdown, choose: List
In the Source box, type:
=IF(A1="Yes",FullList,Fixed)
Click OK

 

[Ken Sills]

I am not competent in VBA, so that's not an option for this near-ter
requirement.

Debra, thanks so much for your very descriptive suggestion. I wil
certainly give it a try tomorrow.

Rgds, Ke

 

[Debra Dalgleish]

You're welcome. If you have any problems with it, post back to this thread.