msgbartop
Just another WordPress site
msgbarbottom

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:

=SUM(B2:Last)

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:http://www.dailydoseofexcel.com/archives/2004/06/30/dynamic-data-validation/This 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.