Validation in Excel

[Dimitris]

Hi,
My question: Is it possible to validate cells within a workbook by using named ranges in another workbook?

I need to validate a number of cells in many Excel workbooks, by allowing only entry of values from a list (named range of cells) in a different central workbook.
What I want to achieve is to be able to change the allowed values only in the list of the central workbook, and have automatically the new values appearing in the drop-down validation boxes in all other workbooks. Thanks in advance
Dimitris

 

[Debra Dalgleish]

You can do this if the other workbook is open. There are instructions here:

http://www.contextures.com/xlDataVal05.html

Or, link to the list in the master workbook, and use the linked list as the source for the data validation list.

 

[Dimitris]

Hi Debra, thanks a lot for the solution.
I finally had to go the second way:
I put the lists of valid entries in the worksheet ‘lists’ in a central spreadsheet: [central.xls]lists; I created the worksheet ‘validation’ in the spreadsheet to be validated (validated xls); I linked the cells in the [validated.xls]validation to the corresponding cells in [central.xls]lists; I named the columns in ‘validation’ worksheet; I validated the cells necessary in the validated.xls by using Data->Validation->Settings->Allow:List and entered the name of the ranges I previously defined. Now, I can easily change the allowed values in [central.xls]lists and the allowed values are reflected in my choices when I enter values in the validated.xls

 

[Debra Dalgleish]

Hi Dimitris,

You're welcome, and thanks for describing what you did to get the data validation working.

Debra
--
Debra Dalgleish

Hi Debra, thanks a lot for the solution.
I finally had to go the second way:
I put the lists of valid entries in the worksheet ‘lists’ in a central spreadsheet: [central.xls]lists; I created the worksheet ‘validation’ in the spreadsheet to be validated (validated xls); I linked the cells in the [validated.xls]validation to the corresponding cells in [central.xls]lists; I named the columns in ‘validation’ worksheet; I validated the cells necessary in the validated.xls by using Data->Validation->Settings->Allow:List and entered the name of the ranges I previously defined. Now, I can easily change the allowed values in [central.xls]lists and the allowed values are reflected in my choices when I enter values in the validated.xls

You can do this if the other workbook is open. There are instructions here:

http://www.contextures.com/xlDataVal05.html

Or, link to the list in the master workbook, and use the linked list as the source for the data validation list.