So I’m finally finishing up the scripts and such for the book. Yes, they are very, very late. But you guys will benefit from that because you’ll get a few things which wouldn’t have been in there had I released ’em when Pro SQL Server 2008 Failover Clustering was released.

Anyway, I was creating the final versions of the checklists and configuration worksheets using Office 2010. I started them in Office 2007,  but have since installed 2010. I’m working with a client this week and I sent them the latest version of the configuration worksheet. The worksheet contains a lot of dropdown boxes to make it easier in some cases to select a value. The customer said they couldn’t see the dropdown. I was baffled. Both Office 2007 and Office 2010 have the same format (.xlsx). I wasn’t aware of any changes. Since I didn’t have Office 2007 installed, I couldn’t figure it out. I was baffled.

Today, I installed Office 2007 in one of the virtual machines on my laptop. Lo and behold, the customer was 100% correct. So what’s the difference?

In Office 2007, to create a dropdown with a list of values on another sheet, here’s what you do:
1. On the sheet where you want (other than the one you’re working on), create the values.
2. Select the values.
3. In the Name box (next to the formula [fx] under the ribbon) enter a name for those values.
4. On the sheet where you want to use those as a dropdown, select a cel.
5. Select the Data ribbon.
6. Select Data Validation, then Data Validation.
7. On the Settings tab, select List in the Allow dropdown.
8. For Source, enter the equals sign and then the name you just created. An example would be =MyList.

In Office 2010, you no longer have to define a name for the list before using it. You are allowed to directly select the data in Step 8, meaning that steps 2 and 3 no longer need to be done.

While this is actually more intuitive, when you save it does not pop up a warning message (like it does for other instances) where this would cause a problem for previous versions of Excel. Now, I didn’t select to save this as an .xls (the 97 – 2003 format). I did .xlsx which is the same format as Office 2007. So while the file format is the same, something is clearly different. The file does not error out in Office 2007 at all; it just shows the last selected value in the cel, no dropdown. Annoying.

The moral here: if you are creating documents in Excel 2010 that use dropdowns, and need them to be backwards compatible, do things the 2007 way.

Sometimes living on the bleeding edge isn’t the best thing. Although you’ll get both 2007 and 2010 versions of my files when I put them up for download soon, so maybe you guys all benefit from my pain …