Excel 2007 : Drop Down Lists

[whitepaw]

HI, I am trying to set up a drop down list where some of the data involved (The year) is on another Worksheet. I know how to do this when all the data is on the same worksheet but it doesn't seem to work when some data is on a different worksheet. The attached example shows a working drop down list where all the date is on the same worksheet but in the same example immediately below, some of the data is in sheet 3, and i cannot seem to include that data in a working drop down list. Please help.

[DonkeyOte]

I confess I don't really follow what you're trying to do based on the file but in general terms if you wish to use ranges from another sheet in terms of Data Validation etc then you must use Named Ranges - for more info. on naming ranges see: http://www.contextures.com/xlNames01.html

[Mike_S]

not very clear what you want though I have a feeling that you are having problems creating the validation list on a separate sheet from where the list is. What you should do is create a named range of the cells with the list. You can then copy and or create the validation drop down in any sheet. I have attached your spreadsheet with the changes.

Hope that solves your problem.

Mike

[whitepaw]

Thank you both for your help and I apologies if I didn't describe the problem very well.I included the attachment to demonstrate that I understood how to make a named range but that I couldn't make it work with data from a different sheet. Thanks Mike_S for your suggestion to simply copy the named range from the other sheet. I will also read DonkeyOte's link to see if there is a way to apply data from different sheets to make up a named range. Cheers