how to increment the number of a row unless the previous row does not contain a number

Hi there.

Your description is a bit ambiguous. But... a picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

UNLESS one of the cells is empty (does not have a formula at all)

Not possible if you are referencing the cell above in Column A.

See attached

=IF($B9="","",MAX($A$8:A8)+1)

Glenn is asking to see yur WORKBOOK, not a picture of it.

Your excel file.... the .xlsx from which the screenshot came. A picture of your sheet is non-editable. I want to see a file, with an indication fo your expected results in it.

I am sorry Glenn, I am new here.
Here's the example Excel sheet attached:

do you mean :
if the cell on the right is not empty, then auto increment the number from the very first line of data, and of empty it will be empty too, and if the cell below it has the data, the numbering will continue automatically?

Edit : I make 2 tables with 2 different formula

Well actually I would like to base it on the row above, not the cell to the right and in particular I'd like a formula that doesn't reference a specific row with an absolute row address, but just references the row above with a -1 relative address because I have a ton of rows and they get moved around so they need to renumber automatically based on whether the row above has a number (or not renumber if the row above is empty). Make sense?

I don't think you have understood what you were told in Post 2. Any cell can have EITHER a value OR a formula. But not both.

Your sample didn't help as there was no explanation in it. I am struggling to understand what you want.

Does this do what you want:

Formula:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

You will need VBA to do this BUT initially your "blank" cells will need a character [e.g. "X" ] entered to identify them as needing to be skipped.

the attached is a simple example: the highlighted cells in column B had an "X" and the range was arbitrary value of B8:B30

It can only be done with VBA and even then you will have to identify cells to be left blank (post #13).

And how do we know when to stop adding the data ?

Did you try what was suggested?

Try this:

=IF(OR(B8="",B8="x"),"",MAX($A$7:$A7)+1)

$A$7 is an absolute reference, but $A7 is relative and will increment as you drag copy down.

Try it.

If you want this all in column A with no reference to column B, then as has been pointed out already, it can't be done with a formula, and you;ll need to go down the VBA route.

As Ali said, $A7 is not an absolute row reference. Space or "x" is a new requirement, as far as I can tell, so not included in my solution. Looks like Ali has done the necessary tinkering

In A8:

Formula:

Please Login or Register  to view this content.

dragged down as far as you need. If you subsequently delete the formula in a cell, the count will move down.

May be better to use INDEX.

Formula:

Please Login or Register  to view this content.

Try:

Formula:

Please Login or Register  to view this content.

Updated. Please check.

If you want a formula to drag down

in A9

=MAX($A$8:A8)+1

then just delete formula entries where you want a blank or add in non-numeric data

Please try A1 (then copy down and delete some cells that you want)

=IF(ISNUMBER(OFFSET(A1,-1,0,,)),MAX(OFFSET(A1,-ROW(A1)+1,0,ROW(A1)-1))+1,"")

note : this formula will add 1 from max of all above cells

Regards.

Removed by JT

Pretty sure post #24 worked for that.

The formula in the workbook in post #24 assumes you copy it down as far as you wish. Then you can clear the formula from any cell, or replace the formula with any alphabetic text. You CANNOT put a numeric value in.

You're welcome.