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.
5 Answers
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)I've seen Excel do this before, and I'm never certain why. You can fix this problem though by using the E:E notation to refer to the whole column.
=OFFSET($E1,0,0,COUNTA($E:$E),1)If you need something like the example shown in your screen captures, you can use the following.
=OFFSET($E$3,0,0,COUNTA($E:$E)-COUNTA($E$1:$E$2),1)this is because Excel's behaviour of a relative reference in named ranges is different than in a formula. Change to absolute reference and it will work.
BR
1If you switch formula referencing into R1C1 you can view the actual behavior.
In this mode addresses used in names are always the same when moving to a different cell, because this notation show relative addresses as they are:
R for current row, R[1] is one row down R[-1] one row up etc... (same is for columns)
When using standard A1 referencing, names with relative addresses are automatically translated by Excel showing the resulting target cell.
So really this is a feature, you can define a name pointing current row in column D (RC4) and use it anywhere in the sheet to refer current row in column D.
I just stumbled upon this problem with a workbook. The problem wasn't limited to just Excel 2010, but happened with 2016 as well.
The fix for us with unchecking the Lotus Formula Transition options at the bottom of the Advanced Options. I'm not exactly sure what those transition options do, but it seems they mess with range names in formulas.
0
