3

I know this drag and drop in spreadsheets.

sheets

I want the first column with auto-increment. But I have thousands of record so how can I auto increment by any formula for a specific range or up to the bottom of spreadsheets.

I want formula like: 

=A1+1
=row(A1)+1
Community
  • 1
  • 1
Dinesh Gurjar
  • 498
  • 5
  • 17
  • If your first cell is static value, then you can use the below formula This formula has to be place in `B2` `="s"&MID(B1,2,LEN(B1)-1)+1` Select the range and control + D Head's up : Using this formula will increase the Excel size(in-case you want this output for more rows). I would suggest to have VBA code for this process – Praveen DA May 09 '19 at 07:54

1 Answers1

7

For a specific range (ex: first 100 rows)

=Arrayformula("S"&row(A1:A100))

Or, all the way to the bottom:

=Arrayformula("S"&row(A:A))
JPV
  • 26,499
  • 4
  • 33
  • 48
  • working fine but if i want to start from 101 to 2000 then – Dinesh Gurjar May 09 '19 at 08:04
  • 1
    If you'd want row 101 to be the first number, you can try inr row 101 (or where ever you want) =Arrayformula("S"&row(A101:A2000)-100) – JPV May 09 '19 at 08:42
  • thanks. this one solve my problem =Arrayformula(row(A101:A2000)) – Dinesh Gurjar May 09 '19 at 09:12
  • @JPV How to set the end range to last non-empty cell? I'm getting the `Argument must be a range.` error with this `=Arrayformula(row(A5:"A"&INDEX(B2:B,COUNTA(B2:B),1)))` using this answer to get the last non'empty cell in columnn `B` https://stackoverflow.com/a/43141556/10789707 – Lod Aug 11 '23 at 10:22
  • Found the sequence method `=SEQUENCE(INDEX(B5:B,COUNTA(B5:B),1)+1, 1, MIN(B5,INDEX(B5:B,COUNTA(B5:B),1)+1), 1)` but still curious of a way with a string if possible. – Lod Aug 11 '23 at 11:20