Data Validation Error Alert not Working

[mvancleave]

I have applied data validation to certain columns based on named ranges in another sheet.

Data/Validation Select "List" Source =NamedRange

I have selected the "Show error messages ... " box and added message text, but the alert does not come up when invalid data are entered.

The ranges are defined dynamically using the offset and counta functions. When I define the names statically, everything works fine.

What can I do to fix this, short of using static name defintions?

Thanks,

Mike

 

[wilsoff]

Note: Becareful when using COUNTA for dynamic ranges. If you have blanks in the range the dynamic formula will fail. The above method of using MATCH is my preference because it allows for blanks.

Thanks for this - it saved me hours. Mine wasn't working. Swapping the spaces with a full stop fixed the issue!