Continuous numbering with blanks - multiple worksheets

0
votes

In Sheet1 I have 50 cells for numbering (column B) and my formula is =IF(ISTEXT(C9),B8+1,"").

There will always be 50 rows and some could remain blank if there is no text in column C.

I would like to continue numbering on Sheet3, all the way through sheet20. So, if Sheet1 actually ends on number 10, I would like sheet3 to continue to number at 11, and if sheet 3 ends on number 20, I would like sheet4 to continue to number at 21, etc.

I have tried multiple formulas, but am having difficulty because there are blanks. Your help is much appreciated!

2
excelexcel-formula

2 Answers

0
votes

Use "MAX" to get the higher number, and use "INDIRECT" to look at the last sheet. So assuming you're using default names on the sheets, start by getting the current sheet name:

A1=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")

Then get the sheet #.

A2=RIGHT(A1,LEN(A1)-5)*1

Multiplying by 1 gives you the number instead of the text. Now we can get the previous sheet name:

A3="Sheet"&A2-1

Now use Indirect to get the highest value in column C on the previous sheet:

A4=MAX(INDIRECT(A3&"!C:C"))

If we combine it, we get:

=MAX(INDIRECT("Sheet"&RIGHT(REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),""),LEN(REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),""))-5)*1-1&"!C:C"))

As long as your data starts in Row 2 (i.e. you have headers), here is the full formula as well:

=IF(ISTEXT(C2),IF(OR(ROW()=2,IF(ROW()=2,TRUE,MAX(INDIRECT("B2:B"&ROW()-1))=0)),MAX(INDIRECT("Sheet"&RIGHT(REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),""),LEN(REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),""))-5)*1-1&"!C:C"))+1,MAX(INDIRECT("B2:B"&ROW()-1))+1),"")
0
votes

One way would be to put: =IF(ISTEXT(C4),COUNT(Sheet1!B4:B13)+1,"") in first cell of Sheet2, and then reuse =IF(ISTEXT(C5),B4+1,"") in all cells below.

If you would like to have all the cells with the same formula, you could move that COUNT to a hidden row just above the first one of your data.

Then you do the same thing with next sheet, just changing the naming.