Copy workbook- Validation function

[sjs]

I have a "master" workbook that contains customer contacts. In an effort to
update these contacts I created several columns that contain drop-down list
validation cells. I'm using cell range titles for my validation drop-down
list options that are contained in it's own sheet within the workbook.

I copied each saleperson's contacts into their own workbooks and brought
over the validation sheet with the copy. In each of the copied workbooks the
validation cells are intact but when I click on the drop-down arrow the
options aren't presented. When I click on Data-Validation...I see the
validation criteria copied over properly but when I click on "OK" I get the
error "The Source currently evaluates to an error. Do you wish to continue?".
When I select Yes the validation range still does not present itself.

Any help would be appreciated.

tks,
steve

[Patti]

Try redefining your cell range title in each of the workbooks -- sounds like
the validation is looking for the list in your master workbook instead of the
list in the salesperson's file.

"sjs" wrote:

> I have a "master" workbook that contains customer contacts. In an effort to
> update these contacts I created several columns that contain drop-down list
> validation cells. I'm using cell range titles for my validation drop-down
> list options that are contained in it's own sheet within the workbook.
>
> I copied each saleperson's contacts into their own workbooks and brought
> over the validation sheet with the copy. In each of the copied workbooks the
> validation cells are intact but when I click on the drop-down arrow the
> options aren't presented. When I click on Data-Validation...I see the
> validation criteria copied over properly but when I click on "OK" I get the
> error "The Source currently evaluates to an error. Do you wish to continue?".
> When I select Yes the validation range still does not present itself.
>
> Any help would be appreciated.
>
> tks,
> steve

[sjs]

Hi Patti,

That fixed the problem, thanks! But I wonder why it's happening, I have too
many copied sheets and too many validation lists to correct manually...

tks,
steve

"Patti" wrote:

> Try redefining your cell range title in each of the workbooks -- sounds like
> the validation is looking for the list in your master workbook instead of the
> list in the salesperson's file.
>
> "sjs" wrote:
>
> > I have a "master" workbook that contains customer contacts. In an effort to
> > update these contacts I created several columns that contain drop-down list
> > validation cells. I'm using cell range titles for my validation drop-down
> > list options that are contained in it's own sheet within the workbook.
> >
> > I copied each saleperson's contacts into their own workbooks and brought
> > over the validation sheet with the copy. In each of the copied workbooks the
> > validation cells are intact but when I click on the drop-down arrow the
> > options aren't presented. When I click on Data-Validation...I see the
> > validation criteria copied over properly but when I click on "OK" I get the
> > error "The Source currently evaluates to an error. Do you wish to continue?".
> > When I select Yes the validation range still does not present itself.
> >
> > Any help would be appreciated.
> >
> > tks,
> > steve

[Patti]

I think it happens because the named range doesn't exist in the salesperson's
file. I don't know of another fix. I've avoided the problem by moving my
validation lists rather than copying them -- I move the worksheet to the new
file and close the old file without saving -- this way, the worksheet and the
named range exist in both files.


"sjs" wrote:

> Hi Patti,
>
> That fixed the problem, thanks! But I wonder why it's happening, I have too
> many copied sheets and too many validation lists to correct manually...
>
> tks,
> steve
>
> "Patti" wrote:
>
> > Try redefining your cell range title in each of the workbooks -- sounds like
> > the validation is looking for the list in your master workbook instead of the
> > list in the salesperson's file.
> >
> > "sjs" wrote:
> >
> > > I have a "master" workbook that contains customer contacts. In an effort to
> > > update these contacts I created several columns that contain drop-down list
> > > validation cells. I'm using cell range titles for my validation drop-down
> > > list options that are contained in it's own sheet within the workbook.
> > >
> > > I copied each saleperson's contacts into their own workbooks and brought
> > > over the validation sheet with the copy. In each of the copied workbooks the
> > > validation cells are intact but when I click on the drop-down arrow the
> > > options aren't presented. When I click on Data-Validation...I see the
> > > validation criteria copied over properly but when I click on "OK" I get the
> > > error "The Source currently evaluates to an error. Do you wish to continue?".
> > > When I select Yes the validation range still does not present itself.
> > >
> > > Any help would be appreciated.
> > >
> > > tks,
> > > steve