Data Valadation from another worksheet in the same workbook

[jonnykats]

i have a workbook that has 4 work sheets. i want to have a drop down list on sheet on that shows info from sheet 4. i know how to make the list ei. data>validation>list and i select me refrence. but i try to type
='sheet 1'!e4:e10 but i get an error saying i can reference another sheet in the workbook.. or anysheet except the one the data i want to list from.. any ideas? thanks

 

[MarkAndrews]

Try naming the range then in the validation part =range name

As i understand it, the list or range has to be contained in the same workbook in order for this to work

 

[jonnykats]

PERFECT! THANKS!

 

[Robert W]

You have to trick excel into letting you use other sheets or other workbooks. First Create a dummy list of data in your worksheet in A1 thru A3. Choose Insert-name-define and give it the name you want for your good list. In referes to highlight your dummy list. It should look like this =Sheet1!$A$1:$A$3 Select the cell where you want your list and Choose data-valadation,in the allow box choose allow list, in the source box type =(your name for your list). Now you can change where your list is located. If your list is in another workbook you need to open it. Go back to your work sheet where you want your list and select insert-name. select the name you gave your good list. Swipe the refers to box clean, with your mouse click on the work book and sheet where your good list is. Highlight your good data and click ok. If its in another work book called pricedata on a worksheet called customers in A1 thru A60 it would like this =[pricedata.xls]customers!$A$1:$A$60 This works in Excel 2003. If your good list is in another work book, both work books must be open for your list to work.