Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel Data Validation Drop Down Boxes

Posted on 2013-01-30
16
336 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 46

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 46

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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 

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 46

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
 
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 46

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 46

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 46

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

856 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