• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Indirect vs offset for fetching cells from differen worksheets

Hellomoto

New Member
What are the differences between Indirect and offset? Both functions can be used to perform the similar tasks. When is one better than the other?
 
OFFSET is a volatile function (recalculated every time) that can be used to give position of cell(s) by giving numerical column/row offsets, as well as changing the size of the cells.


INDIRECT needs a string reference built in order to be used, possibly leading to a need for concatenation. It can't be used for a reference on an external workbook. However, it's greatest strength, imo, would be that you can use if for an ABSOLUTE NO WAY THIS IS GONNA CHANGE referehce. I.e.,

=INDIRECT("A2")

will always refer to A2, regardless of the user inserting/deleting rows, moving things around. In some scenarios, this is a great benefit.


In the end, it really depends on your needs/use, but I usually go with OFFSET because I find it easier to use, and more versatile.
 
Hellomoto


Adding to what Luke said, Indirect and Offset are both Volatile functions and are calculated everytime a calculation event is triggered even if they are not directly linked to the change.


The Index function is not volatile and can be used especially with the Match function to great effect in looking up and retrieving data.


Offset's greatest attribute is that it can return a Range to another function eg: Sum(Offset( )) and then the parameters of Offset can be used to change the area which is summed interactively.


Indirect requires a string input and these can be difficult to construct to achieve good results, although using R1C1 notation with indirect can make them a lot simplere to write.


You should note that when you use a Volatile function with a Non-Volatile function the entire equation then becomes volatile eg: =indirect(offset( )) will result in a volatile equation
 
Back
Top