New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Dynamic Ranges - Part1/3 - Using OFFSET function

Free Excel Tips and Tutorials

Dynamic Ranges - Part1/3 - Using OFFSET function

Postby pecoflyer » Mon Apr 09, 2012 8:01 am

Many posts are related to data sources which may grow or shrink with time.
Therefore I decided to post some tips which can be referred to if necessary when posting an answer.

This tip is about Dynamic Ranges using the OFFSET function

Applies to : all the XL versions for PC( I don't know about MAC but I suspect it's the same

Need to know :
    OFFSET function
    How to Define Names

How it works :
this time I will refer you to the following link which has all necessary info :
http://www.contextures.com/xlNames01.html#Dynamic

Drawbacks
    OFFSET is a volatile function ( more info at http://www.decisionmodels.com/calcsecretsi.htm about volatile functions)
    The OFFSET syntax might be a bit awkward
    It will recalculate each time something is changed on the worksheet, slowing down large sheets or sheets with many calculations
    Entering a formula in the Define Name input box is not always easy

Alternatives

See part 2 and 3 which will be coming soon
A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Return to Excel Tips and Tutorials

Who is online

Users browsing this forum: No registered users and 20 guests