Change Data Validation Automatically

[kumara_faith]

Hi,

I have the following:

Excel Workbook
	B	C	D	E	F
2	Dept	Staff	*	Dept	Staff
3	M_1	Jake	*	M_1	Jake
4	*	Mila	*	M_2	Micah
5	*	Kate	*	*	*
6	M_2	Micah	*	*	*
7	*	Clyde	*	*	*
8	*	Ruth	*	*	*
Sheet1

Cell F3 and F4 are data validation with indirect formula to list only staff from M_1 or M_2 based on column C. I have used the indirect formula in data validation to refer to named range of M_1 and M_2. The named range for M_1 is from C3:C5 and the named range for M_2 is from C6:C8.

When the drop down menu is selected in cell F3, only staff name Jake, Mila and Kate will appear.

If I change the name Jake in cell C3 to Lenie, I want the name Jake in cell F3 also to change to Lenie automatically.

Is this possible ? Appreciate all the help.

 

[kumara_faith]

bump.

 

[kumara_faith]

bump.

 

[number kruncher]

You could just run 'Circle Invalid Data' to check Data Validated Entries.

Or you could use conditional formating with a COUNT function to test if the entry is still valid, to prompt a manual change.

 

[kumara_faith]

Hi Number,

Thank you for your post. However, my database is quite huge and there is a need often to change the validation entries. I used to do this manually but it is really time consuming. A solution that changes automatically will really help.

Is this possible ?

 

[kumara_faith]

bump.