Forum Discussion

R. Stahl's avatar
R. Stahl
Copper Contributor
May 15, 2017
Solved

Excel 2013 table wrong formula extension with 'mask'

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 entr...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    May 15, 2017

    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

     

     

R. Stahl's avatar
R. Stahl
Copper Contributor
May 15, 2017

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

  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    May 16, 2017

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

  • R. Stahl's avatar
    R. Stahl
    Copper Contributor
    May 15, 2017

    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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 15, 2017

    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

     

     

Resources