Attend Excelapalooza
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
    Posts
    62
    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 12:36 AM.

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    1,492
    Post Thanks / Like
    Mentioned
    13 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
    Posts
    62
    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
    1,492
    Post Thanks / Like
    Mentioned
    13 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
    Posts
    62
    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
    1,492
    Post Thanks / Like
    Mentioned
    13 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
    Posts
    62
    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
    1,492
    Post Thanks / Like
    Mentioned
    13 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
    Posts
    62
    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
    24
    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.

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
  •  

 

DMCA.com