Excel 2013 table wrong formula extension with 'mask'

[R. Stahl]

Dear all,

just experienced some strange habit in Excel, version 2013. I've created a table with some columns, the first for numbering. The table starts in cell B6 with headline, subsequent data entries. In B7 the formula is

=rows(B$7:B7)*(length(C7 & D7)>0)

This results in '1', as long as at least one of the neighbouring cells C7 or D7 contains anything. This works fine for the first line. Now I format the range as table and subsequent use the ancient command 'mask' to enter data.

But now I find, that these data are entered, but the formula is modified, so that all newly added rows contain the same vale in the first (i.e. number-) column. Excel marks them as error and I can correct it, but as soon as I enter new data, the same behaviour appears.

That is weird and makes it a bit painful to use the table. Seems, there is some programming failure - or I don't understand the process good enough?

Thanks for any suggestion. I hope, my wording is ok, for I use Excel in a not-english-language version...

[#2]

just found out, the same behaviour appears when entering data manually without the use of the command 'mask' . Seems to be a problem of the table-definition.

BR

[#3 · Accepted solution]

Hi,

With the table try to use (if "b" is the name of the column)

=ROWS(B$7:[@b])*(LEN(C7 & D7)>0)

to take the rows between first one and the current. Or even better

=ROWS(B$7:[@b])*(LEN([@c] & [@d])>0)

With autoexpanding of the table initial B7 is interpreted as the last row in the table and substituted accordingly

[#4]

It's a known bug.

Excel Magic Trick 1041: Expandable Range In An Excel Table For Running Total (Zack Barresse's Trick)...

Running Total in a Microsoft Excel Table

[#5]

Thanks guys,

I got it running! Didn't expect it to be a known problem - and even would have known how to write a search phrase to find it.

Now I know, what to do. Thank you very much, indeed.

BR

[#6]

What do you mean with "command 'mask'"????