How to dynamically change the list in drop down list based on thevalue of another cell?

X

xu8810152

Hi all,

My question is that I have a drop down list containing, say 3 products (apple, orange, banana) in cells A1:A3. Usually I want the drop down list to list all 3 products. But when the value of cell B1 changes from 1 to 0, I want the drop down list to only list the first product contained in cell A1. Can this be done easily, without having to use VBA?

Thanks!
 
B

benmcclave

Hi,

You can do this with named ranges. For example, you could name the full list (A1:A3) "Full" and name the shorter list (cell A1) "Short". Then put this formula in the data validation formula bar: =if($B$1=0, Short, Full).

Ben
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pick from drop-down list gives empty or erroneous result 6
Colour-coding a cell from a drop down list 1
Drop-down list dependant on another cell value 2
Excel Excel: How do I put a formula in a drop down list 11
Define the Range of a drop-down list 5
calculate a value on a new cell based on the list 1
Data validation drop down list selection change 4
Drop down list - link to another worksheet 4

Top