Data Validation List Not Working Correctly

  • Thread starter Thread starter Mike D
  • Start date Start date
M

Mike D

I'm trying to validate data that is being entered into a cell based on a
list that is on another worksheet in the same workbook. I have the range
with the list set up on Sheet 3 and the cell I'm trying to validate against
this list is in Sheet 1. I've set up a Defined Name for the Range on Sheet
3. When I set this up in the data validation screen, I pick list and I set
the Source equal to the name of the range. I also ensured that on an error
the program should stop. I can see the list in the pull down menu, but when
I enter data that is not in the list, the error is never trapped. If I put a
list on the same worksheet, the error will trap. Is there something I'm
missing? I'm working with Excel 2003.
 
I'd make sure that "Show error alert..." on the Error Alert tab of the Data
Validation dialog is checked. I have used the technique you described many
times and it has always worked.

--
Jim
| I'm trying to validate data that is being entered into a cell based on
a
| list that is on another worksheet in the same workbook. I have the range
| with the list set up on Sheet 3 and the cell I'm trying to validate
against
| this list is in Sheet 1. I've set up a Defined Name for the Range on
Sheet
| 3. When I set this up in the data validation screen, I pick list and I
set
| the Source equal to the name of the range. I also ensured that on an
error
| the program should stop. I can see the list in the pull down menu, but
when
| I enter data that is not in the list, the error is never trapped. If I
put a
| list on the same worksheet, the error will trap. Is there something I'm
| missing? I'm working with Excel 2003.
| --
| Mike D
 
Jim,
Thanks for responding. I did check and I do have the alert box checked.
It still isn't flagging the error. Any other ideas?
 
No. Can you send me an example (slimmed down if possible)?

--
Jim
| Jim,
| Thanks for responding. I did check and I do have the alert box
checked.
| It still isn't flagging the error. Any other ideas?
| --
| Mike D
|
|
| "Jim Rech" wrote:
|
| > I'd make sure that "Show error alert..." on the Error Alert tab of the
Data
| > Validation dialog is checked. I have used the technique you described
many
| > times and it has always worked.
| >
| > --
| > Jim
| > | > | I'm trying to validate data that is being entered into a cell based
on
| > a
| > | list that is on another worksheet in the same workbook. I have the
range
| > | with the list set up on Sheet 3 and the cell I'm trying to validate
| > against
| > | this list is in Sheet 1. I've set up a Defined Name for the Range on
| > Sheet
| > | 3. When I set this up in the data validation screen, I pick list and
I
| > set
| > | the Source equal to the name of the range. I also ensured that on an
| > error
| > | the program should stop. I can see the list in the pull down menu,
but
| > when
| > | I enter data that is not in the list, the error is never trapped. If
I
| > put a
| > | list on the same worksheet, the error will trap. Is there something
I'm
| > | missing? I'm working with Excel 2003.
| > | --
| > | Mike D
| >
| >
 
Jim,
I figured out why it wasn't working. I had a blank line in the named
range I was using for a list. I just selected an entire column for the list
because the data that is being copied into that sheet could vary in length.
This was a poor man's attempt to allow for a dynamic range.
I need to set up a real dynamic named range. I know how to do that. I
defined a name with the follow formula under source:
"=OFFSET($A$1,0,0,COUNTA($A:$A),1). This was set up for the range on Sheet 3
of the Workbook. I then added the Data Validation to Sheet 1 of the workbook
and called out the named range in the Data Validation dialog box. When I hit
return, it crashes the program. I assumed you would call out the named range
just as you would call out the named range for a static named range. Is
there something different I need to do? I didn't wnat to have to add another
column of data into Sheet 1 to copy the data over from Sheet 3.
 
Jim,
I figured out my error. When I used the formula to create the dynamic
list, I needed to specify the worksheet name within the formula. Otherwise,
it got confused as to which worksheet to run the formula. Once I added the
worksheet name everything is working fine. Thanks for responding to my posts.
 
Back
Top