Data validation across worksheets/workbooks

[Kloot8]

How can I validate data across workbooks?
I'd like to set up an data entry form, where the user has to assign a unique ID to an item. To make sure the ID is a unique one, I thought I could transfer previously assigned IDs into a column *of another workbook*, and set a validation rule in the worksheet form's data entry cell that validates that the chosen number is not present among those in the column in the column of the other workbook. To my disappointment, Excel told me that data validation cannot be used across worksheets/workbooks. Is there a workaround, or can anyone think of another way to warn the user that a previously assigned ID has been entered ???

Thanks for your help!!
Klaus

 

[Ivan F Moala]

On 2002-04-26 04:15, Kloot8 wrote:
How can I validate data across workbooks?
I'd like to set up an data entry form, where the user has to assign a unique ID to an item. To make sure the ID is a unique one, I thought I could transfer previously assigned IDs into a column *of another workbook*, and set a validation rule in the worksheet form's data entry cell that validates that the chosen number is not present among those in the column in the column of the other workbook. To my disappointment, Excel told me that data validation cannot be used across worksheets/workbooks. Is there a workaround, or can anyone think of another way to warn the user that a previously assigned ID has been entered ???

Thanks for your help!!
Klaus

Use a named range for your data validation range. ie. name your range in the other sheet
and use this in your list.

 

[Kloot8]

Ivan,

What is 'named range' ??

(Sorry for the dumb question)

 

[Chris Davison]

Kloot,

(from the film ?)

a named range is a range of cells which you assign a name...

so rather than a range of A1:Z100, you can call it "Klootsummary" or something more appropriate

to facilitate this :

insert
names
define

and follow the boxes

 

[sen_edp]

hello there

try this

http://www.mrexcel.com/board/viewtopic.php?topic=4489&forum=2&start=0

i think it helps a lot