Data Validation

[sgaurav]

How to do data validation in sheet 1 and cell A4 if the list of Data is stored in a different sheet...lets say "Sheet2". So what I am asking is I have data in sheet 2 but I want to do data validation in sheet1.

Thanks

 

[Aladin Akyurek]

Select the cells in Sheet2, go to the Name Box on the Formula Bar, type SList, and hit enter.

Use

=SList

as Source

in the cell (on Sheet1) you want to have a dropdown list.

 

[sgaurav]

I am sorry I didn't quite understand it..can you please email me some easy instructions...btw where is this name box located...is there any way you can just go to data and then validation and then select the list from different sheet?

Thanks

 

[Aladin Akyurek]

I am sorry I didn't quite understand it..can you please email me some easy instructions...btw where is this name box located...is there any way you can just go to data and then validation and then select the list from different sheet?

Thanks

Where is the list on Sheet2 exactly -- A2:A10, C4:K4,...?

And which cell(s) do you want to validate on Sheet1?

 

[sgaurav]

The data is in "Sheet2" and the range is DC11:DC202

Thanks

 

[Aladin Akyurek]

The data is in "Sheet2" and the range is DC11:DC202...

Great, but you forgot to answer: "And which cell(s) do you want to validate on Sheet1?"

When the list is in some other sheet than the sheet where you want to data validate a cell, you have to use names for ranges instead of ranges themselves.

Select DC11:DC202 on Sheet2.
Go to the Name Box on the Formula Bar, type SList, and hit enter.
Activate the cell on Sheet1 where you want to choose from SList.
Activate Data|Validation.
Choose List for Allow.
Enter in the box for Source:

=SList

Click OK.

 

[muradist]

Aladin Akyurek, just want to say you thank youu!!

 

[vasa]

In Excel 2007 and I think in 2003 as well it works to put in source the range as =[sheetname]![First cell of the range]:[Last cell of the range]

In our example it goes like:
Activate the cell on Sheet1 where you want to choose from the list
Activate Data|Validation.
Choose List for Allow.
Enter in the box for Source:

=Sheet2!DC11:DC202

Click OK.

(Error: "You cannot use references to other worksheets or workbooks for Data Validation criteria" appreas if you have only one cell as lists source. For any range from another sheet even =Sheet2!A1:A1 it works perfect.)

 

[Aladin Akyurek]

In Excel 2007 and I think in 2003 as well it works to put in source the range as =[sheetname]![First cell of the range]:[Last cell of the range]

In our example it goes like:
Activate the cell on Sheet1 where you want to choose from the list
Activate Data|Validation.
Choose List for Allow.
Enter in the box for Source:

=Sheet2!DC11:DC202

Click OK.

(Error: "You cannot use references to other worksheets or workbooks for Data Validation criteria" appreas if you have only one cell as lists source. For any range from another sheet even =Sheet2!A1:A1 it works perfect.)

It's a best practice to assign a name to a range like Sheet2!DC11:DC202 and use the assigned name as list source in a cell one wants to data validate.

 

[tra1a]

Likely going to have an duh moment in a few, but what is meant by "Activate Data"

using 2007

Thanks