Drop Down Listed in Excel '10 stopped working

Microsoft Microsoft excel 2007 full vers...
July 25, 2011 at 21:51:55
Specs: Windows 7
I had one drop down list in my multisheet workbook setup and functioning normally. Suddenly, the drop down list is not functioning and reinserting the drop down list does not work, that is, on no sheet in the workbook does inserting a new drop down list work. However, if created in a new worksheet, drop down lists insert and function normally.

I think my worksheet is currupt or I've done something to the worksheet/workbook that Excel is clearly very unhappy about!

I'd be happy if the Community could give me some ideas as to what's wrong. The workbook is a little complex, but contains no sensitive data and I am happy to e-mail it for your assessment. My e-mail address is xxxxxx.xxx, drop me a line or if there is a way to post the file for community access, please let me know, although I'd prefer to send it to individual community members as the file is the crux of my research thesis.


Many thanks,
Djwaunnie

edited by moderator: email address removed


See More: Drop Down Listed in Excel 10 stopped working

Report •


#1
July 26, 2011 at 03:35:32
I have deleted your email address from your post.

I am less concerned about you receiving a lot of spam than I am about this site becoming known as a place where email addresses can be harvested. Once the bad guys start hanging around, we will be open to all sorts of security issues.

If someone wants your email address you can give it to them via private message.

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 26, 2011 at 04:41:27
Is your drop down list based on a named range?
Is the option for autocalculate turned on? (I can't remember if that can be done on a sheet by sheet basis or the whole workbook.)
Did you create this workbook in 2010?
Does the problem reproduce itself on other versions?
Does the problem reproduce itself on other computers?
Oh, and your title suggests 2010, yet the tag shows 2007. What version are you working on?


Just waking up and those were a few questions that popped into my head.


Report •

#3
July 26, 2011 at 06:48:36
To DerbyDad03: Thank you for protecting me from a thoughtless mistake, your deletion is well appreciated.


Dear Kitty,

Thank you for your reply.

Is your drop down list based on a named range? Yes, it is. It lists 10 names: Sugar, manufacturing, tourism etc.

Is the option for autocalculate turned on? (I can't remember if that can be done on a sheet by sheet basis or the whole workbook.) The option in Formulas -> Calculation Options -> Automatic is ticked.

Did you create this sheet in 2010? The 6 data sheets of the workbook were created in Microsoft Excel 2010, (part of my Office Professional Suite).

Does the problem reproduce itself on other versions? Yes, I used a copy of my file on a different machine which is running Excel 2007, the drop down list function does not insert/work in any cell on any of the worksheets of my file. On a new, blank/empty workbook however, the drop down function inserts and runs normally.

Does the problem reproduce itself on other computers? See answer above.

Oh, and your title suggests 2010, yet the tag shows 2007. What version are you working on? I am using Excel 2010 installed on Windows 7.

Regards,
Djwaunnie


Report •

Related Solutions

#4
July 26, 2011 at 07:01:36
just to clarify you are using a named range for your validation list

on sheet ##

in arbitraty cells A1:A10 you have 10 names. You have defined these 10 cells A1:10 as "Some_Special_Name"

In data validation you have choosen list and you entered =Some_Special_Name

right now all I can think of is the possibility that on the original sheet you pointed to a list of names elsewhere on the sheet say X1:X10. This works for the original sheet
when you copied to a subsequent sheet the values in X1:x10 were not copied to each sheet. However now when you are creating new sheets you are copying the entire original sheet which grabs the X1:10 so it works on those sheets.

I cant really think of any reason why it would not work without seeing your workbook and this is all I can think of for now. I just know that depending on how you set up your data validation it can vehave differently on different sheets. I also remember something about data valation lists need to be on the same sheet if you are not using a named ranged.


Report •

#5
July 26, 2011 at 07:39:38
Dear Kitty,

How do I go about sending you the file privately? I think it may be easier for you to see/experience the problem first hand. Not sure how to private message, an option mentioned earlier by DerbyDad03.

Djwaunnie


Report •

#6
September 5, 2011 at 05:34:47
I have this same Problem. I originally made the spreadsheet in an older version of excel. I have now upgraded to latest version of excel. It works fine on my PC but when I email the spreadsheet to anyone, regardless of the version of excel, the drop down lists dont work anymore. I have used the same spreadsheet for 2 years and it's only since windows 7 and or excel 2010 that this issue has arisen.
Neil

Report •

Ask Question