Excel – Named ranges changing randomly in Excel 2010

microsoft excelmicrosoft-excel-2010

I'm trying to create some named ranges in Excel that extend using the offset function based on the number of non-empty cells in a column, with the general formula:

=OFFSET($E1,0,0,COUNTA($E1:$E99999),1)

The name manager accepts the formula, but when I go back into it it seems to have changed some of the numbers randomly. The named ranges do not function as excepted. See screenshots below (before and after).

This looks to me like a bug in Excel, but I can't find any mention of it on Google. I'm running Excel 2010 (14.0.7128.5000) on Windows 7.

before
after

Best Answer

I've just run into the same problem when I was trying to type in addresses for named ranges, rather than highlighting the range to select it.

It seems that if you enter a relative address in the format =A1:B2 for a named range, Excel treats the range as being relative to the current cell at any time. If you move the cursor to a different cell, then check the Name Manager again, you will see a different range.

To fix this, you need to use fully absolute addresses in the format =$A$1:$B$2.

In the example in the question, the column references are already absolute, but the row references are relative. The following should work:

=OFFSET($E$1,0,0,COUNTA($E$1:$E$99999),1)
Related Question