Confusion abounds when going from Excel on a Windows machine to Excel on a Mac. Part of the problem is not knowing what the Function keys will do. Here’s a couple of links to help straighten things out.
At the bottom of the Recent Workbooks jump list, Excel 2010 has an option that you can check to: Quickly access this number of Recent Workbooks: xx
Checking this box adds recent files to the left pane of the File Menu so you can always see the file names. Any files you have pinned to the top of the list will be at the top, even if they haven’t been opened in a while.
This is nice because sometimes when you click on the File menu you’re immediately defaulted to the Info tab for the current file. Now Quick access Recent Workbook files show regardless.
The only way to have the source data for a cell-drop down list on another worksheet in Excel 2003 is to create a named range. In the Data Validation dialog box any reference to another worksheet range gets the following warning message.
You can put your values on the same worksheet, but mostly they tend to overcrowd the worksheet and its really much nicer if you stick all lookup values on another worksheet, hidden from view.
In the picture below the named range “theWeek” was created on worksheet 2, and then entered as the Source in the Data Validation dialog box. This circumvents not having source data on another worksheet for your cell drop-down list.
This same problem exists in Excel 2008 for Mac, but the solution is the same.
Excel removed the requirement of having List Source data on the same worksheet for Data Validation in the newest versions of Excel, 2010 and 2011. The picture below shows a Data Validation dialog box from cell A2 on Worksheet 1, and you can see the Source reference for the List is on Worksheet 2.
Other Versions of Excel
Excel 2007 won’t let you select a data range on another sheet, but if you type in the range as shown above you won’t get an error message or nasty pinging sound, and it will actually work.
Excel 2003 and 2008 are another matter, and I’ll share a tip in my next post on how to get around this restriction.
I found out today through a reader comment (by @Eric) in my Excel Semi-Pro blog, that there’s something called Microsoft Equation 3.0 that’s in Excel versions 2003, 2007, and 2010. It creates an embedded object and is different than the new Equation Editor in Excel 2010 and Word 2011, which I wrote a article about yesterday.
I’ve never used this Microsoft Equation Editor 3.0 before, but briefly checked it out by going to Insert > Object and selecting Microsoft Equation 3.0 from the list. Here’s what I got in Excel 2003.
It didn’t seem very user friendly via my quick look. And all three Windows versions have the same program. (Kinda ugly to say the least)
But that got me to wondering if Excel for Mac has the same program so I checked Excel 2008 and 2011 to find Microsoft Equation, which seems a little more user friendly than its Windows counterpart.
Alas, neither program is a match for the new Equation Editors in Excel 2011 and Word 2011. But good to know in a pinch, eh?