Drop down list

[Ayoub99k]

I made a list. When I click on a cell, a drop down list appears with the numbers 1,2,3 and 4.

What I want to do is. If a cell = the value 500000 then what appears in the list is say for example 2,3,4 and if the value in a cell = the value 300000 then the values that appear in the drop down list is 1,2,3,4.

Any way I can do this?

[FDibbins]

There is no need to create a new thread when asked to change your thread title. My comment in your other thread...
http://www.excelforum.com/excel-prog...313-lists.html
...explained how to change your title.

Also, as I mentioned in your other thread...

Also, dropdown lists are manually selected. If you want the cell contents to change, based on some other value, use vlookup or IF (if there are only a few options)

[Ayoub99k]

All I want to do is

If cell B6 = 500000 then the values in the drop down list IS 1,2,3,4

And if cell B6 = 300000 then the values in the drop down list is 5,6

[FDibbins]

OK do you mean that if B6=500000, then the only options in teh DD are 1, 2, 3 or 4?

[Ayoub99k]

yes correct.

and if B6 = 300000 then the options in the drop down list would be 6,7

[FDibbins]

The way I would do this is to create 2 lists, 1 for 500000 and 1 for the other value, then give each list a name - I called them pick500000 and pick300000.

Then use this as a LIST in Data Validation...
=INDIRECT("Pick"&$B$6)

[Ayoub99k]

That's awesome! I managed to do this. Funny enough my current spread sheet I have used tons of the INDIRECT function for other stuff. But I didn't know we could use it for data validation and lists. Thanks )

[Ayoub99k]

Just a question. Is it possible to add the INDIRECT function to a spin button's CELL LINK?

[Ayoub99k]

Hmm btw. Suppose in one list I want values 1,2,3,4,5 all in a list. How do I make that happen? Coz in the code you provided:
=INDIRECT("Pick"&$B$6)

You made it cell B6 right? But in B6 we can just put one value. How to add multiple values?