Wednesday, August 18, 2010

Dynamic Ranges in Excel - low maintenance

I like Named Ranges in Excel. They free you from remembering to $A$1 every time you need to refer to an absolute block. And Named Ranges allow you to set up a reference block away from the worksheet that contains the Data Validation in-cell drop down list.

However, one more thing, Named Ranges still don't autoexpand to handle more items added later. Use ranges overlaid onto Lists (Excel 2003) or Tables (Excel 2007 and later). Or use OFFSET and COUNTA formula directly in the Name Manager specification.

in reference to: Excel Video Lessons | Dynamic Validation (view on Google Sidewiki)

Surprise, Surprise - the VLOOKUP cheatsheet

Here are a bunch of VLOOKUP references

Monday, August 16, 2010

Interesting app

Haven't tried it yet - works with Powerpoint and OneNote - Microsoft Interactive Classroom - you can poll and quiz participants in a class

in reference to: Download details: Microsoft Interactive Classroom (view on Google Sidewiki)

Friday, August 13, 2010

The catalogue of Excel functions

Often, in Excel classes, participants ask about Excel functions and formulae. Of course, our job as trainers is to show and tell about functions specifically how to key them in, how to handle keying errors and recover from syntax errors. I tend to tell people that Excel has so many functions (and even more in the Excel 2010 version) that as generalists we don't have the domain intimacy with specific theory used in a function. Easily getting a list of function names in the catalogue is important. Walkenbach has written several Excel books and Excel function books. Here is a listing of the functions, with hyperlinks to the Microsoft Office website.

in reference to: The Spreadsheet Page Excel Downloads: Excel 2007 Function List (view on Google Sidewiki)

Monday, August 9, 2010

Connect Access 2010 to cloud computing Azure

Interesting. Remember when IT snobs say that Access is lightweight? Not big iron? How about when Access front ends to Windows Azure?

in reference to: Access 2010 and SQL Azure - Microsoft Access - Site Home - MSDN Blogs (view on Google Sidewiki)

The comprehensive Excel Range Name Manager has been updated

JKP and collaborators have updated their Name Manager to support 64 bit Excel 2010. Really useful if you dig Range Names

in reference to: Name Manager (view on Google Sidewiki)

Created custom menus in Excel

Here's an short and sweet article from JKP Application Development Services about the difference in custom user menus in classic Excel vs Excel 2007.

in reference to: Menu's (view on Google Sidewiki)