Excel 2007和Excel 2010之间的下拉列表兼容性问题 [英] drop down list compatibility issue between Excel 2007 and Excel 2010
问题描述
如果我使用Excel 2010打开Excel 2007文件,在插入下拉列表的单元格中,对包含列表的单元格的引用会有所改变,下拉列表显示错误的数据。
例如,如果我指定使用列表中的数据,单元格C20的值为C25,当我使用Excel 2010打开文件时,单元格将更改为D15:D20。我没有在引用的单元格的移动中找到趋势。
如何在不使用Excel 2007仅适用于2007年文件的情况下防止这种情况,而Excel 2010 2010文件?
我找到了适用于我的解决方法。
如果这是您的原始下拉框公式:
= Sheet2!A1:A5
然后将其更改为:
= INDIRECT Sheet2!A1:A5)
对于我来说,在Excel 2007中保存Excel 2010之后,在Excel 2007中打开时,会阻止它删除下拉列表。我发现这是偶然的,因为我需要INDIRECT来定义我的下拉范围,因为它有所不同。
Jeremy
I prepared a spreadsheet where I used the drop down list command in several cells (data validation, list). I needed to provide two versions of the same spreadsheet: one saved as Excel 2007 file, and the other one as Excel 2010 file.
If I open the Excel 2007 file with Excel 2010, in the cells where I inserted the drop down list, the reference to the cells that contain the list is somehow changed and the drop down list shows the wrong data.
For example, if I specify to use as the data in the list the values of cells C20:C25, when I open the file with Excel 2010 the cells are changed to D15:D20. I did not find a trend in the "shifting" of the referenced cells.
How do I prevent this without using Excel 2007 for the 2007 file only and Excel 2010 for the 2010 file only?
I have found a workaround that works for me.
If this is your original dropdown box formula: =Sheet2!A1:A5
Then change it to: =INDIRECT("Sheet2!A1:A5")
For me, this stops it removing the dropdown when opening in Excel 2007 after saving in Excel 2010. I found this out accidentally because I needed INDIRECT to define my dropdown range, as it varied.
Jeremy
这篇关于Excel 2007和Excel 2010之间的下拉列表兼容性问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!