Cell based drop down and text

You can only make it work once.

Make a list in datavalidate (Data--> Datavalidate) with N/A;Yes;No in cell C4
Then Write =IF(D2="N/A";"N/A";"Y") in C4
Then delte N/A from the list in datavalidate

IT Works as long at you only change i D2 and not choce on the list in C4. When you do that, the IF-formula disapeares.

A Macro will be more solid.

Thanks for your input Birthel.

I have tried with name manager, the problem is when we change the parent cell drop-down list getting updated and leaving the text what we have selected last time.

I need only when I select N/A the destination has to N/A if I select Yes a drop-down of Yes No and if I select again N/A the drop-down to disappear and N/A should come.

Would appreciate if any suggestions. Thank you..!!

Hi Richard,

I have uploaded a new file in my original post, can you please check now. Thanks

Hi
You can't lock cells by chosing in a datavalidation.
You have to use a control and submit a macro.

(developer)
Attachment 740609
Attachment 740610

(The screen are from Danish Excel, but til buttons are at the same place)

It can be something like this:

With ActiveSheet.Range("C4").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Y,N"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

and to lock:
ActiveSheet.Range("C4").Locked = True

Best regards.

Hello Birthel,

Thanks for your coding.

Can I please have the working sheet?

Thank you.