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]

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]

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.