=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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.