Data Valadation from another worksheet in the same workbook

jonnykats

New Member
Joined
Oct 11, 2006
Messages
21
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
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
 

Robert W

New Member
Joined
Mar 22, 2008
Messages
1
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.
 
Last edited:

Forum statistics

Threads
1,141,626
Messages
5,707,483
Members
421,510
Latest member
haroonstr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top