I am trying to use data validation to prevent out of range numbers that would make my "named ranges" become invalid. The named ranges are used to set ranges to chart data. When testing, if I enter a number that is out of the range specified, I do get an error message indicating that the number is out of range. However, when I cancel out of the data validation error, I get an additional error that says "Excel found a problem in one or more formula references in my worksheet". Also,my charts become corrupted with the ranges messed up and changed colors on my columns.
The whole reason I am using data validation is to prevent my formulas from becoming invalid. It seems that Excel tries to calculate them even when the data is out of range.
Does any body have any suggestions on why this is happening?
I am using Excel 365 on Windows 10.
