Default value in data validation, in-cell formula gets lost when dropdown used

[wilkisa]

I think I need a VBA statement to make this work but going to the experts to guide me. My user has a workbook with a data validation in cell D2 that has been named TOYS. The validation list contains 5 different toys: Baby Dolls, Balloons, Balls, Bears, Bikes. In a questionnaire below, each row has a question that is answered via a data validation beginning in E7. The validation options are Yes, No, N/A. In certain questions, if Baby Dolls, Balloons, Balls, or Bikes was chosen from the TOYS list, we want the validation to always show N/A automatically so the answerer will not need to respond to that question. However, if Bears is the chosen toy, then we want the answerer to be able to choose Yes or No.

For the scenario above, I can make this work, however, I'm told this questionnaire will be used over and over again and the formula gets deleted when the answerer chooses the Yes or No option from the data validation. How can I prevent the formula from being deleted from this cell? Is there an easier to perform these tasks? Looking for and appreciating any help I can get.

 

[Tom Urtis]

Sounds like, depending on what's selected by data validation in cell D2, you want a formula in (using your example) cell E7 and that you also want cell E7 to be data validated for just Yes or No. If that's the case, and it were me, I'd scrap that approach unless you invoke DV with a Change event for cell D2 to handle the cells where Bears is chosen. I'd hide the rows where those N/A question cells are located so you don't need to adjust their contents because the user doesn't need to see them, and unhide those rows when just Bears is chosen. But you can keep all rows visible by re-establishing each question's answer cell in real time at every change in cell D2. It'd come at a potentially (albeit tiny depending on system resources) cost of coding overhead but you have those options.

 

[wilkisa]

It just never occurred to me to set those rows as hidden. I can do that and I think that will solve my issue. Thanks for the suggestion, Tom!