3

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

2
  • 1
    Never seen this. Did you do anything else other then close named range manager and open it again. It looks like you may have deleted some rows which can change your formula. Also based on the number being near the total number of rows of the worksheet it almost seems like you pressed ctrl+down when entering the formula. – gtwebb Aug 20 '14 at 19:39
  • The behaviour is quite erratic. If I set the formula, accept changes and close the name manager, then go back into it, it seems to change. I certainly haven't deleted any columns/rows. The original formumla is typed manually. – Snorfalorpagus Aug 20 '14 at 19:46
7

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)
2
  • 1
    Just had this baffling problem at hand as well. Your observation is 100% correct - thanks! – Carl Colijn Oct 11 '17 at 14:16
  • Oh my god, why excel. I thought like... I was having RAM fail, or stray neutrino bursts or something. This is seriously the oddest way to program this. – Monica Apologists Get Out Aug 29 '18 at 20:19
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)
0

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

1
  • 1
    This duplicates information in JRI's answer. – fixer1234 Jul 16 '19 at 6:36
0

If 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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.