Just another WordPress site

16 Feb 09 Relative range names in practice

With range names you can do big tricks with excel.I will show you one of these tricks today:When you define a range name, then excel will propose the absolute cell reference as default.But you can use a relative or a mixed reference too.The active cell is the starting point.You have to create your reference like you would write into that cell.My trick is:

  1. Select A2 cell.
  2. Choose Insert –> Name –> Define
  3. Type a name for the range: “Last”
  4. Write in the refers to box “=A1″ without quotes and without dollar signs.
  5. Give a name to this range, for example “last”

This range name will refer to the upper cell to the current position.It can be used like this:


will give you a dynamic range that will grow or shrink when you insert or delete some rows, even if the deleted row is the last row from the range.This is a perfect solution.If you insert multiple rows and don’t fill every cell, the normal reference will not upgrade, the relative name will always work.

Reader's Comments

  1. |

    If you ever want to see a reader’s feedback :) , I rate this post for four from five. Detailed info, but I have to go to that damn msn to find the missed pieces. Thank you, anyway!

  2. |

    Concerning “Relative range names in practice”; Can this somehow be used when a dropdown list (Data validation) is required?I tried to use it this way, but without positive result!Your answer is most apriciated.

  3. |

    Deisko, the solution for the dropdown validation is the Dynamic range names.For example:=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)You can read more about this here: works without code too.

  4. |

    @Zoltan TillThnx… I will give it a try!

Leave a Comment

You must be logged in to post a comment.