Understanding the Offset function and its use in creating dynamic named ranges opens up some very powerful techniques in Excel.
One such use is in creating a dynamic chart which updates automatically when its linked data is changed.
Although this can be more easily done in Excel 2007 onwards by using Tables the dynamic named range method is more powerful and has a wider range of uses.I have used these techniques extensively in my Excel work.
This excel file available in the Excel techniques section of www.mathsfiles.com/Excel_Files shows how the Offset function works and how it can be used to create a simple dynamic graph.
Offset Function Excel file
You can also watch a Demo which shows in more detail how to set up a dynamic named range and use it to create a dynamic graph.
Watch the Demo (based on Excel 2007) or
Watch the Demo (based on Excel 2003)