These references are often called relative and absolute references. Dynamic (or relative) references (e.g. A1:C5) change when the formula they are contained within is copied to other cells. For example, if the formula is copied one cell down, then the references in the formula are changed by one row (e.g. they would become A2:C6). If the formula was copied two cells right and four down then the range reference would change relative to this move (e.g. they would become C5:E9). This is extremely useful as it means that a formula can be set up to work against given cells, and repeated so that it can work against other cells in the same row or column. They are called dynamic references because the cells referenced change when copied to new locations. They are called relative because they change relative to how far away from the original formula’s position they have moved.
Note that dynamic references do not change when they are moved (e.g. using cut). Note also that when a reference is to a named range (a name allocated to a range reference, created by selecting cells, and choosing Insert, Name, Define) then this is considered to be static.
Static (or Absolute) Ranges do not change when the formula is copied. When a reference is static, it has dollar signs in front of each part of the formula which is static.
Why do I need static and dynamic
ranges ?
Within a formula entry, it is often desirable to have some aspects of the formula dynamic, and some aspects static. For example, if we have lists of store reference numbers in our cells on the right, and have their corresponding names in a range of cells on another sheet, we might look up the store name using the formula ;
=VLOOKUP(A2,Sheet2!$A$1:$B$10,2,0)
The reference A2 is dynamic, and the reference Sheet2!$A$1:$B$10 is static. If we copy the formula entry to the next cell down, the reference A2 will change relative to the number of cells it has moved by – i.e. it will become A3. The reference $A$1:$B$10 will not change however – because it is static. This is desirable because although we want to look up a different value, we want to look in the same range of cells where the store names are listed. If the range $A$1:$B$10 had been written A1:B10, then it would have changed relative to the formula move to A2:B11, and if the store reference number we were looking up was in cell A1, it would not have been found.
This is a common cause of error in a VLOOKUP, and the VLOOKUP Assistant will warn you if any part of the range reference you’re looking into is dynamic.
Mixed
references.
Range references can have a mixture of some parts dynamic, and some parts static. For example, if our store data sheet not only contained the store name, but also 4 lines of the address across 4 extra columns, then the VLOOKUP may have been coded as ;
=VLOOKUP($A2, Sheet2!$A$1:$F$10,2,0)
We could copy the formula down one row to reflect the store number in cell A3, or we could copy it across one cell, and change the ,2, part to ,3, part to find the first part of the store’s address for the store in cell A2 – copying the formula over to the right hasn’t changed the reference because the dollar appears before the column part of the cell reference. Because the row part of the cell reference does not have a dollar sign in front, it changes relative to the new position of the formula.
The VLOOKUP Assistant will count the number of parts of the reference which are dynamic, and warn you if there are any.