drag down serial number/ incremental wise upto thousands of cells

[goodstudent]

Hello,

Kindly see below screenshot...I need to drag this down serial number/ incremental wise upto thousand of cells. The N/A cell will keep filling with 1 number increasing. If I keep holding and pulling down it will take me a long time. The last cell in the thousands of cell to fill up has been filled with N/A as well.
Now how to fill this quickly without having to pull down for so long...

Thanks a lot in advance for your guidance & support!

 

[awoohaw]

remove any data that is in cell D540145 and any cell below that that has data.

 

[goodstudent]

Hello. Please wait. I am working this out on a fresh excel sheet.

 

[goodstudent]

Hi. I understand that in that excel sheet, the final value of D cell is integrated in the formula of another cell and hence, there is SPILL erorr.
So, I tried this in fresh excel sheet. Now, this is working but my cells are not continuing from A971580 from where the formula started. It is starting from A000001. So kindly get this sorted out for me, please. Start it from A971580 onwards.

 

[goodstudent]

Hello,

I have found an alternative solution based on the formula you provided. I will just create the sequence on a fresh sheet based on the formula you provided, then I will copy paste the value from A971580 to A999999 to my D column on my excel sheet. Voilà !
It is a slight turnaround but provides me a quicker solution then dragging down for a long time.

 

[awoohaw]

okay...
I'm happy you found something that works.

But to answer your prior question, to start at the cell below 971579, do this:

Put the last row number (no letters) in the formula below where it says "LASTCELLROW".
="A"&TExt(SEQUENCE(LASTCELLROW - 971579,1,971580,1);"000000")

So, if you want to go to row 1000000,

="A"& TEXT(SEQUENCE(1000000 - 971579,1,971580,1);"0000000")

 

[Scott Huish]

You could just start there

="A"&SEQUENCE(28420,,971580)