Add validation list on condition, remove list upon condition --> for one cell value

Hi all,

My first post on this forum. It has helped me a lot thus far but after a big search I can't find an answer to my current problem.

This is what I'm trying to achieve:
(GOAL1) If cell A1 is value X, put list validation on cell A2 with options from another sheet - no other value may be entered than this list!
(GOAL2) If cell A1 is not value X, remove the validation form cell A2 and allow any value.

What I've tried that came close:
(TRIED1) Uncheck the warning that comes when wrong data is entered in the validated cell; this way any value AND there is a list.
(TRIED2) Looked at a lot of solutions using nested validation lists with the INDIRECT function, this did not solve my problem.
(TRIED3) Insert a conditional statement into the custom section of validation; something like =IF(A1="X";optionsfromanothersheet;*). Thought behind this was if A1 is X then validate using the list called optionsfromanothersheet, otherwise allow wildcard (anything).

It occurred to me that what I'm looking for is actually switching validation types upon a condition. Validation type 1 being a restricted list, type 2 allowing everything.

I hope you can help and that my problem is clear to you; thank you in advance.

Kind regards,

Hugo

Hi Hugo,

I have used indirect function before to convert a data validation list into two lists depending on the calue in a cell - this appears to be the nested validation in your try 2.

Have just discovered that you can use this method - however for your Goal 2 simply have the list refer to a cell that = the value in A2.

Pretty sure that will work for you.

if you need help - upload a spreadsheet.

CHeers