It requires some non-trivial VBA, but you could add a Worksheet_Change event handler to the sheet to validate the data using the Target cell and the named range of the validation table. This gives you a lot of options on how to handle invalid data - a pop-up error message (msgbox) or moving the cursor back to the invalid cell are two.
For situations where a simple warning will suffice, I've used conditional formatting to automatically highlight a cell with an invalid or missing value. Here's the formula:
I was puzzled at first about the assertion that for Excel 2007 and earlier, "data validation lists cannot be placed on other worksheets". I routinely do this in both 2003 and 2007 and it seems to work fine. I simply use a defined name for the range of cells that covers the data for the validation list.
It turns out that the issue is due to the "table" or "list" feature in Excel. As I didn't know about this feature, I had never used it. (BTW thanks for that into - I love learning new stuff). I simply used a named range for my validation data.
Of course, when I want to insert items, I must be sure to insert *within* the list so that the named range is updated properly. Often, I then have to copy/paste or sort the data to put it where it needs to be in the list. But I inadvertently avoided the "different sheet" issue this way.
So I thought I'd mention this for users who really want to put the data validation list on another sheet. Just use a global named range for the values and it'll work fine. Just be sure to insert new entries between existing ones so that the defined name gets updated as needed.
My answers
It requires some non-trivial VBA, but you could add a Worksheet_Change event handler to the sheet to validate the data using the Target cell and the named range of the validation table. This gives you a lot of options on how to handle invalid data - a pop-up error message (msgbox) or moving the cursor back to the invalid cell are two.
For situations where a simple warning will suffice, I've used conditional formatting to automatically highlight a cell with an invalid or missing value. Here's the formula:
=ISNA(VLOOKUP(J7,DataTable,2,FALSE))
Tom the Toolman
I was puzzled at first about the assertion that for Excel 2007 and earlier, "data validation lists cannot be placed on other worksheets". I routinely do this in both 2003 and 2007 and it seems to work fine. I simply use a defined name for the range of cells that covers the data for the validation list.
It turns out that the issue is due to the "table" or "list" feature in Excel. As I didn't know about this feature, I had never used it. (BTW thanks for that into - I love learning new stuff). I simply used a named range for my validation data.
Of course, when I want to insert items, I must be sure to insert *within* the list so that the named range is updated properly. Often, I then have to copy/paste or sort the data to put it where it needs to be in the list. But I inadvertently avoided the "different sheet" issue this way.
So I thought I'd mention this for users who really want to put the data validation list on another sheet. Just use a global named range for the values and it'll work fine. Just be sure to insert new entries between existing ones so that the defined name gets updated as needed.
Tom the Toolman