Unique and data validation

[Giordano Bruno]

Has anyone found a solution to this problem. I create a column of names using SORT(FILTER(UNIQUE(... The source is the names in my data entry sheet. I then use these values in a data validation drop down. When an invalid name is entered, the new name is immediately added to the sorted and filtered unique list so the new name is never invalid and therefore is not trapped by data validation. I can turn calculation to manual on activating the worksheet and automatic on deactivation and when the worksheet changes, I run Automatic then Manual. This is cumbersome and I must then set calculation to automatic when the workbook is deactivated. I feel there must be a simpler solution.

 

[GlennUK]

Put calculation to automatic as a command in the Workbook_Close code.