Solved

Excel Data Validation Drop Down Boxes

Posted on 2013-01-30
16
311 Views
Last Modified: 2013-02-05
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
Comment
Question by:akafiti
  • 6
  • 6
  • 4
16 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38837298
The sheet is probably protected.
0
 

Author Comment

by:akafiti
ID: 38837312
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38837361
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
 

Author Comment

by:akafiti
ID: 38837393
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38837425
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
 

Accepted Solution

by:
akafiti earned 0 total points
ID: 38840400
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38840495
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38840528
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 26

Expert Comment

by:redmondb
ID: 38840585
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38840608
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38840677
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38841316
Sorry Brian I didn't mean to argue with you, I was just prompting akafiti to try named ranges before he gave up.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38841452
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38841529
I'm not sure that it's unreported. While it doesn't refer to named ranges,  check this out which says in part

...
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38841660
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
 

Author Closing Comment

by:akafiti
ID: 38854299
Limitations in Excel 2007
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now