Hessel de Walle's Blog

Normally when you use the OFFSET function to create dynamic names, it will use absolute cell referencing . So, you will get examples like this: =OFFSET(Sheet2!$A$1;0;0;COUNTA(Sheet2!$A:$A);1) So, what if we used relative cell referencing instead? The result would depend on the cell our cursor is in when we create our formula and the cell our cursor is in when we are looking at the name again. Fill the range A1:A4 with the figures 12 to 15. Put your cursor in A1 again. Create a name RANGE based on the formula with relative cell referencing : =OFFSET(Sheet1!A1;0;0;COUNTA(Sheet1!A:A);1) Now put your pointer in B15 . Look at the name again: As you can see, we get a different formula now based on the position of our cursor. So for what purpose could we use relative cell referencing ? On a sheet with the name relativeoffset ,I created a table in A1:J18 like this: Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 ...