# Thread: Auto numbering of rows basing on true values

1. ## 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?

2. ## Re: Auto numbering of rows basing on true values

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

3. ## 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. ## 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. ## Re: Auto numbering of rows basing on true values

Originally Posted by footoo
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. ## Re: Auto numbering of rows basing on true values

Originally Posted by masud8956
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. ## Re: Auto numbering of rows basing on true values

Originally Posted by footoo
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. ## Re: Auto numbering of rows basing on true values

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

9. ## Re: Auto numbering of rows basing on true values

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

Thanks a lot!

10. ## Re: Auto numbering of rows basing on true values

Originally Posted by masud8956
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

#### Posting Permissions

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