Google Sheets auto increment column A if column B is not empty

Question

I want to auto increment column A if column B is not empty. For example if I type anything to B14, I want A14 to auto increment.

How can I do this?

This one for later reference with empty cells in between: https://stackoverflow.com/questions/75449393/how-to-increment-only-if-cell-is-not-empty-on-input-with-ifna-in-google-sheets — Lod, Aug 08 '23 at 18:46

score 2 · Accepted Answer · answered Aug 01 '22 at 12:02

2

if B column is not interrupted by an empty cell you are good with just:

=SEQUENCE(COUNTA(B2:B))

answered Aug 01 '22 at 12:02

player0

score 1 · Answer 2 · answered Aug 01 '22 at 12:07

1

If column B is interrupted (or even not) by empty cells you could use

=INDEX(IFNA(VLOOKUP(ROW(A2:A),
    {FILTER(ROW(A2:A),(B2:B<>"")),SEQUENCE(COUNTIF(B2:B,"<>"),1)},2,0)))

answered Aug 01 '22 at 12:07

marikamitsos

score 1 · Answer 3 · answered Aug 01 '22 at 12:14

Here are some methods that may suit your needs...

This will auto-sequence while jumping blank rows, so will be the number of filled cells/rows:

=ARRAYFORMULA(IFERROR(MATCH($B$2:$B&ROW($B$2:$B),FILTER($B$2:$B&ROW($B$2:$B),$B$2:$B<>""),0)&"."))

This will auto-sequence while skipping blank rows, so the number will be the row of data (not the row number):

=ARRAYFORMULA(IF($B$2:$B<>"",ROW($B$2:$B)-ROW()+1 & ".",""))

or:

=ARRAYFORMULA(IF($B$2:$B<>"",SEQUENCE(ROWS($B$2:$B))&".",""))

And this will autofill every row up to the last row with a value:

=ARRAYFORMULA((QUOTIENT(SEQUENCE(SUMPRODUCT(MAX(($B2:$B<>"")*(ROW($B2:$B))))-1)-1,1)*1)+1)

3 Answers3