Data Validation

Topic

New Reply

Lounger,

I have data validation using the following formula – =OFFSET(Planner!$C$8,0,0,COUNTA(Planner!$C:$C),1)

This works fine, however it appeas that there is no protection to prevent invalid data being entered into the cell as with normal data validation.

Am I doing something wrong or can the offset formula be altered to prevent this.

Any thoughts

Reply | Quote

Replies

I can only add data that is contained within the cells specified by your offset formula.
Can you post a sample workbook that is exhibiting the issue?

Reply | Quote

Mbarron

Thanks for your reply – please see the cut down version of the spread sheet showing the entry into a cell with a data validation list.

Cheers

Reply | Quote

Change the offset to not include the entire C column. Something like “=OFFSET(Planner!$C$8,0,0,COUNTA(Planner!$C$8:$C$100),1).
or
You can keep the Offset() as is but make sure the Ignore Blanks is not checked

and

Make sure the Error Alert Type is set to Stop and not Warning or Information. D7:D9 are Stops, while D10 is set as a Warning will still allow for items not on the list to be accepted

Reply | Quote

Lounger,

I have data validation using the following formula – =OFFSET(Planner!$C$8,0,0,COUNTA(Planner!$C:$C),1)

This works fine, however it appeas that there is no protection to prevent invalid data being entered into the cell as with normal data validation.

Am I doing something wrong or can the offset formula be altered to prevent this.

Any thoughts

When setting up data validation you have the option of setting an error message when invalid data is entered. See attached picture.

Regards,
Maria

Reply | Quote

Thanks for all your replies.

Will give your suggestions a go

Cheers

Reply | Quote