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

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
"Mike D" wrote in message
...
| 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?
--
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
"Mike D" wrote in message
...
| 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

No. Can you send me an example (slimmed down if possible)?

--
Jim
"Mike D" wrote in message
...
| 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
| "Mike D" wrote in message
| ...
| | 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.
--
Mike D


"Jim Rech" wrote:

No. Can you send me an example (slimmed down if possible)?

--
Jim
"Mike D" wrote in message
...
| 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
| "Mike D" wrote in message
| ...
| | 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
|
|

Mike-

There "should" be no problem using a named formula as the source range. See
the attached. If you can't download it send me an email at
.

--
Jim
"Mike D" wrote in message
...
| 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.
| --
| Mike D
|
|
| "Jim Rech" wrote:
|
| No. Can you send me an example (slimmed down if possible)?
|
| --
| Jim
| "Mike D" wrote in message
| ...
| | 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
| | "Mike D" wrote in message
| | ...
| | | 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 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.
--
Mike D


"Jim Rech" wrote:

Mike-

There "should" be no problem using a named formula as the source range. See
the attached. If you can't download it send me an email at
.

--
Jim
"Mike D" wrote in message
...
| 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.
| --
| Mike D
|
|
| "Jim Rech" wrote:
|
| No. Can you send me an example (slimmed down if possible)?
|
| --
| Jim
| "Mike D" wrote in message
| ...
| | 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
| | "Mike D" wrote in message
| | ...
| | | 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
| |
| |
|
|