Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Auto numbering of rows basing on true values

  1. #1
    Board Regular
    Join Date
    Oct 2016
    Location
    Dhaka, Bangladesh
    Posts
    106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Auto numbering of rows basing on true values

    Hi,

    I have a table where I import data from other worksheets. Column A is for row numbers. I have other data in columns B, C and D.

    A3 B3 C3 D3
    1 ...x...y...z
    2....p...q...r

    I need a formula to populate column A automatically with chronological row numbers when there is data in either B/C/D.

    Anyone?
    Last edited by masud8956; Jun 12th, 2018 at 01:36 AM.

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,053
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Auto numbering of rows basing on true values

    =if(counta(b2:d2)<>0,max($a$1:a1)+1,"")

  3. #3
    Board Regular
    Join Date
    Oct 2016
    Location
    Dhaka, Bangladesh
    Posts
    106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto numbering of rows basing on true values

    Thanks!

    My actual range is C90:Q4089. I want the Row numbers in Column B (Row number 1 at B90 and so on)

    I tried
    Code:
    =IF(COUNTA(C90:Q4089)<>0,MAX(B$90:$B90)+1,"")
    and
    Code:
    =IF(COUNTA(C90:Q4089)<>0,MAX(B$89:$B89)+1,"")
    both separately but getting a return of "0"

  4. #4
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,053
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Auto numbering of rows basing on true values

    Put in B90 and fill down :

    =IF(COUNTA(C90:Q90)<>0,MAX($B$89:B89)+1,"")

  5. #5
    Board Regular
    Join Date
    Oct 2016
    Location
    Dhaka, Bangladesh
    Posts
    106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto numbering of rows basing on true values

    Quote Originally Posted by footoo View Post
    Put in B90 and fill down :

    =IF(COUNTA(C90:Q90)<>0,MAX($B$89:B89)+1,"")
    Thanks. It worked. But it keeps returning value against blank rows as well. Is there a way it stops generating numbers upon hitting a blank row in the range

  6. #6
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,053
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Auto numbering of rows basing on true values

    Quote Originally Posted by masud8956 View Post
    Thanks. It worked. But it keeps returning value against blank rows as well. Is there a way it stops generating numbers upon hitting a blank row in the range
    If a number is being returned, it means the row is not blank - i.e at least one of the cells in columns C:Q of that row contains something.

  7. #7
    Board Regular
    Join Date
    Oct 2016
    Location
    Dhaka, Bangladesh
    Posts
    106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto numbering of rows basing on true values

    Quote Originally Posted by footoo View Post
    If a number is being returned, it means the row is not blank - i.e at least one of the cells in columns C:Q of that row contains something.
    Ok. I see it now.

    Actually those cells have got formulas something like
    Code:
    =IF(IFERROR(INDEX(DATA!C$2:C$4001,SMALL(IF(DATA!$C$2:$C$4001>=$R$9,IF(DATA!$C$2:$C$4001<=$R$11,ROW(DATA!$C$2:$C$4001)-ROW(DATA!$C$2)+1)),ROWS(R$9:R9))),"")=0,"",IFERROR(INDEX(DATA!C$2:C$4001,SMALL(IF(DATA!$C$2:$C$4001>=$R$9,IF(DATA!$C$2:$C$4001<=$R$11,ROW(DATA!$C$2:$C$4001)-ROW(DATA!$C$2)+1)),ROWS(R$9:R9))),""))
    Does it mean the cell will be treated as "containing something" even if the cell formula returning nothing?

  8. #8
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,053
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Auto numbering of rows basing on true values

    =if(countif(c90:q90,"")<>15,max($b$89:b89)+1,"")

  9. #9
    Board Regular
    Join Date
    Oct 2016
    Location
    Dhaka, Bangladesh
    Posts
    106
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto numbering of rows basing on true values

    Quote Originally Posted by footoo View Post
    =if(countif(c90:q90,"")<>15,max($b$89:b89)+1,"")
    Worked perfectly...

    Thanks a lot!

  10. #10
    New Member
    Join Date
    Aug 2017
    Location
    India
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto numbering of rows basing on true values

    Quote Originally Posted by masud8956 View Post
    Hi,

    I have a table where I import data from other worksheets. Column A is for row numbers. I have other data in columns B, C and D.

    A3 B3 C3 D3
    1 ...x...y...z
    2....p...q...r

    I need a formula to populate column A automatically with chronological row numbers when there is data in either B/C/D.

    Anyone?
    In either way
    U can use the formula =if(B2="","",A1+1) Drag for respective cells also.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •