Data validation across worksheets/workbooks

Kloot8 · Apr 26th, 2002, 05:15 AM

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 · Apr 26th, 2002, 05:28 AM

Quote:

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 · Apr 26th, 2002, 05:50 AM

Ivan,

What is 'named range' ??

(Sorry for the dumb question)

Chris Davison · Apr 26th, 2002, 01:40 PM

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 · Apr 26th, 2002, 02:40 PM

hello there

try this

http://www.mrexcel.com/board/viewtop...orum=2&start=0

i think it helps a lot

Apr 26th, 2002, 01:40 PM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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 __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 26th, 2002, 05:15 AM	#1
Kloot8 New Member Join Date: Apr 2002 Posts: 2	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

Apr 26th, 2002, 05:50 AM	#3
Kloot8 New Member Join Date: Apr 2002 Posts: 2	Ivan, What is 'named range' ?? (Sorry for the dumb question)

Apr 26th, 2002, 02:40 PM	#5
sen_edp Board Regular Join Date: Mar 2002 Location: Hellas Posts: 553	hello there try this http://www.mrexcel.com/board/viewtop...orum=2&start=0 i think it helps a lot