• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

Excel Data Validation Drop Down Boxes

We have a spreadsheet that was created.  3 of the columns require a drop down box with various options for the users.  The drop down box was created using the 'Data Validations' list feature.  All of the options for the drop down box are stored in a tab within the same spreadsheet.

The person who created the spreadsheet is able to see the drop down box without any issue both locally as well as when he posts it to a shared network drive.  However, any other user, with a Excel 2007 can not see the linked list or drop down options.

Thoughts, suggestions?
0
akafiti
Asked:
akafiti
  • 6
  • 6
  • 4
1 Solution
 
Martin LissOlder than dirtCommented:
The sheet is probably protected.
0
 
akafitiAuthor Commented:
No, it's not protected as far as we can tell. Any user who has Office/Excel 2010 can see the list/drop downs.  I appears to be a compatibility issue with Excel 2010 to other versions.  i think a protected sheet would affect all other users including 2010 users..
0
 
Martin LissOlder than dirtCommented:
Do you refer to a range of cells or do you refer to a named range. If the former than try using the Name Manager to create a name for the range for example Country_of_Origin where the formula might be something like the following if the data's in column F of a sheet named "Form Codes".

=OFFSET('Form Codes'!$F$1,0,0,COUNTA('Form Codes'!$F:$F),1)

And in the Data Validation you would have
=Country_of_Origin
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
akafitiAuthor Commented:
I'll have the guy check who made it.. I'm not sure about that.  The biggest issue is that the 'linked worksheet' with all the data doesn't even appear to be linked on the 2007 versions.. and if we save it as an older version, then it doesn't work for anyone.
0
 
redmondbCommented:
Hi, akafiti.

I appears to be a compatibility issue with Excel 2010 to other versions.
You're quite right. I identified this as the source of the problem in this question.

Briefly, when Excel 2010 edits a file containing a Data Validation drop-down (?sometimes/always/usually?) it includes an entry in the file ("<x14:dataValidations>") which is new in 2010 and is simply not handled by 2007.

Sounds silly, but is there any possibility that your Excel 2007 users can keep this file amongst themselves? Alternatively, you could include an Auto_Open macro in the file which automatically (and silently) corrected the Data Validation when it detects it's running in 2007.

Edit: An easier fix may be to use Names for any external links.

Regards,
Brian.
0
 
akafitiAuthor Commented:
The fix to this, was rather than placing the data to auto-populate in the drop down box on a different worksheet within the same excel file, to put it on that same page the users will work on.  Must be an issue with older Excel versions being able to 'link' to a different tab even though its within the same doc.
0
 
redmondbCommented:
akafiti.

It would be very useful for others with the same problem if you could please take a few minutes to try the following on your working file...
 - Create a Name pointing to the drop-down's validation list.
 - Change the drop-down so it uses the Name for the list.
 - Cut and Paste the list to the other sheet.
 - Save the file.
 - Open and save the file in 2010.
 - Does it still work in 2007?

Many Thanks,
Brian.
0
 
Martin LissOlder than dirtCommented:
At redmondb: I also suggested using named ranges in post ID: 38837361 but akafiti never said if they were already using them or not or if he tried it.
0
 
redmondbCommented:
Thanks, MartinLiss.

Your post didn't mention the probable cause of the problem, and, as you mentioned, akafiti didn't mention any results. Until MS comes through with a fix (not yet, afaik), the more we can tie down this issue the better.

Regards,
Brian.
0
 
Martin LissOlder than dirtCommented:
At akafiti  and redmondb: I was just looking around and found this thread. Take a look at the green 'Answer' post and the one that follows it.
0
 
redmondbCommented:
Thanks, MartinLiss.

Sorry, I'm not sure what those two posts add - we already know that there's an issue between 2007 and 2010 and we already know that Names help. That link doesn't address akafiti's finding about using the same sheet, nor mine about the underlying cause of the problem.

Regards,
Brian.
0
 
Martin LissOlder than dirtCommented:
Sorry Brian I didn't mean to argue with you, I was just prompting akafiti to try named ranges before he gave up.
0
 
redmondbCommented:
MartinLiss,

I didn't think that you were - sincere apologies if I gave you that impression.

I think we're both on exactly the same page about getting more information on the efficacy of Names in dealing with this apparently unreported bug.

Regards,
Brian.
0
 
Martin LissOlder than dirtCommented:
I'm not sure that it's unreported. While it doesn't refer to named ranges,  check this out which says in part

...
0
 
redmondbCommented:
MartinLiss,

Thank you so much for that link - I got too hung up on the attribute "causing" the problem and completely missed this!

I'm afraid it's wrong...
In Excel 2010, you can use data validation rules that refer to values on other worksheets. In Excel 2007 and Excel 97-2003, this type of data validation is not supported and cannot be displayed on the worksheet.
Not only can Excel 2007 handle values on other sheets, it can also handle other books - unlike 2010!

If you're interested, the two files below were created in 2007. The cross-book drop-down works fine (and reflects on the fly changes to the list).  However, the drop-down doesn't work in 2010.

Regards,
Brian.Excel-2007-External-Data-Validat.xlsxExcel-2007-With-Data-Validation-.xlsx
0
 
akafitiAuthor Commented:
Limitations in Excel 2007
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now