Auto numbering of rows basing on true values

[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?

 

[footoo]

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

 

[masud8956]

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

=IF(COUNTA(C90:Q4089)<>0,MAX(B$90:$B90)+1,"")

and

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

both separately but getting a return of "0"

 

[footoo]

Put in B90 and fill down :

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

 

[masud8956]

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

 

[footoo]

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.

 

[masud8956]

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

=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?

 

[footoo]

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

 

[masud8956]

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

Worked perfectly...

Thanks a lot!

 

[Kamal GKJ]

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.