Validation rules in excel not working?

Question

If you Drag cells down to Auto-fill they ignore validation rules?

I've created a two part validation for data entry.  The first allows you to enter a site name from a list of sites.  If you enter an inappropriate name you get an error message, perfect.  Each of those sites has four possible survey stations at it.  I set up a second validation that only allows you to enter one of the 4 stations that go with a given site.  The station names are whole numbers.  If you enter the value in the cell and it is not correct you get the error message I set up.  However if you drag the cell to autofill a column it will increase the value by one (like a normal autofill) but it will allow values that do not fit the validation with no error message.

Why is it allowing cells that don't fit the validation with the drag feature?

Answer 1

Sorry Sunjellie

There is no official way to keep Validation form Auto-fill in excel.

Any member who has similar experiences are welcome to share your solution here.

Regards

Alex

Answer 2

Okay, Thank you.

Is there a way to change this?

For most of the data entry it is not an issue because the cells are being entered individually.  But since the metadata for each of these files is the same for all the records it is common and easiest for these columns to be autofilled after the first entry.  My enterers are weary of the sequential auto-fill but through a couple of hundred files per year one or two always slips through. 

Thanks again for your response

Answer 3

Hi Sunjellie,

When you use Drag to Auto-Fill the cells, it removes the validations you set for the cells, because Auto-Fill have higher priority for cells.

If you have ideas about this, welcome to post and vote for your ideas in Microsoft User Voice.

Regards,

Alex