=IF Statement to control a drop-down box

I have a drop-down box with about 8 number selections in it. It works fine. However, I want to change this drop-down box depending on a condition in another cell, so that if that other cell has the word "No" in it, it forces the drop-down box to now have a zero in it (ideally then preventing the user from entering anything else in the cell). I have tried using Data validation and the type of source code that is supposed to suppress the drop-down box under the correct circumstances (i.e. that the other cell has "No" in it), but what I then get is the last selected number (a positive integer from the drop-down selection) sitting in the drop-down box, which affects all my 'down-stream' data. What I want is to select "No" in the other cell, which will force the drop-down cell to have a zero in it. I hope that makes sense. The cell with the "No" in it could be thought of as a 'master cell' that overrides any function in the 8-selection drop-down box on the condition that the user has selected "No" in the 'master cell' - on which condition the drop-down box is forced to have a zero in it.
BlosMusicAsked:
Who is Participating?
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If you don't mind me charging a little for that - it does go a bit beyond answering questions.

If you are OK with at that, please go to my website www.filecats.co.uk and click "Chat with us" and we can talk further.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
No, doesn't make sense. Maybe give an example.
0
 
BlosMusicAuthor Commented:
OK. My real spreadsheet is very large, and so I'm attaching an example of what I want.
If I put "Yes" in D3, then I want all the drop-down options in D5 to be available; if on the other hand I put "No" in D3 then I want the drop-down options in D5 to go away, leaving me with a non-alterable zero in D5.
Hope that makes sense.
Example-for-EE.xlsx
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
>> If on the other hand I put "No" in D3 then I want the drop-down options in D5 to go away

Please see the attached spreadsheet. I have changed the Diameter range to be variable based on D3.

>> leaving me with a non-alterable zero in D5.

Changing a value in D3 does not alter a value in D5. If you want that, you need to use a macro.
EE150226.xlsx
0
 
BlosMusicAuthor Commented:
Yes, well I guess I need that macro then, as I must ensure that if D3 says "No" then there is no alternative to D5 saying zero, and I must ensure that D5, when it's zero, cannot be altered. I'm not much into macros, but I'm sure I could get there!!
Thanks.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Please see attached.
EE150226-2.xlsm
0
 
BlosMusicAuthor Commented:
Thanks for that. However, I haven't got a clue about how to import that idea into my big spreadsheet. I don't really know how to use macros, that's my problem.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Then you really need to start learning about macros - to teach you how to use macros goes a bit beyond this question.

This is a free book to help you started - http://www.amazon.co.uk/Mastering-Excel-Macros-Introduction-Book-ebook/dp/B00O2OOJ7A/ref=sr_1_2?ie=UTF8&qid=1424947496&sr=8-2&keywords=excel+macros
0
 
BlosMusicAuthor Commented:
Yes, good point. I'll get it. Thanks for your help. I'm some way down the track now, anyway.
Thanks again.
0
 
BlosMusicAuthor Commented:
On reflection, would it be OK if I sent you my full spreadsheet for you to advise me on how to do this thing? I am very willing to get into macros, but I need to solve this issue quite urgently. Trouble is that my spreadsheet is a tad sensitive, commercially, and I don't want it available to others. What do you advise?
0
 
BlosMusicAuthor Commented:
Sorry - I should have completed this. The answer was good, and it helped me greatly.
0
 
BlosMusicAuthor Commented:
It's been a long time, but now that I know how to close a question, i can award points!
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.