Excel Not Filling Formula Correctly Down Column - Blank Row Needed

[Skiier89]

Hello,

I have a list of 100 names displayed vertically in a sheet. I need to modify this list so that there is a blank row between each name (this modified list is then transposed elsewhere in the workbook).

Leaving plenty of space from the original list, my formula is simply =A1 in the first row. The second row is blank. I select both the row that has a formula and then the blank row I've added below. When I drag this selection down, I would expect (and have achieved in the past) Excel to understand there is a blank row included in the selection. For example:

A500 = A1
A501 = Blank row
A502 = A2
A503 = Blank row
A504 = A3
A505 = Blank row

Selecting this section and dragging down worked 6 months ago. The formulas and method did not change, however there are more names in the list now. So when I go to continue the selection, Excel does this instead -

A500 = A1
A501 = Blank row
A502 = A3
A503 = Blank row
A504 = A5

Is anyone able to describe why this is happening or resolve this issue? I understand there are formulas (MOD() and Index()) out there to work around issues like this, however I did not use any function like this originally. I have the same version of Excel installed on my computer, but this method is no longer working. What could be causing Excel to skip a row in the original list instead of recognizing there is a blank row included in the selection I want copied down?

 

[Skiier89]

FYI - this is happening even when I try replacing the blank row with the value equal to the row above it.. Excel is literally skipping over sequential data. For example:

A500 = A1
A501 = A500
A502 = A3
A503 = A502
A504 = A5
A505 = A504

Once again, nothing I did in the past has changed. I'm simply repeating the sequence, and Excel is literally skipping out of sequence. The value in A502 should be = A2 NOT = A3

 

[Peter_SSs]

Selecting this section and dragging down worked 6 months ago.

You must have been doing something different back then*. Excel has always behaved as you are describing it now when dragging such a formula down.

* For example, if you put 1 in a cell and then select that cell and the blank cell below it and drag that down, you will get the sequence 1, blank, 2, blank, 3, blank etc - but that does not involve a formula.

I understand there are formulas (MOD() and Index()) out there ..

I think that you will need one of those or similar. For example this in A500 then select A500:A501 and drag down

=INDEX(A$1:A$100,(ROWS(A$500:A500)+1)/2)