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

  • 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

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)
  • Just had this baffling problem at hand as well. Your observation is 100% correct - thanks! – Carl Colijn Oct 11 '17 at 14:16

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)

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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