Auto fill values with a blank row formatting between entries

[Excel Autofill]

This used to work in MS Excel and I can't figure out why it doesn't anymore...

I have a column of numbers say 1-15 vertically.

I begin a second column and create a formula that directs the first value of
the second column to equal the first value of the first column (1).

I then highlight the formula cell and the cell beneath it and use autofill
to drag both down to create a column with the same numbers as the first
except with rows of spaces between the formulas. 1, 2, 3, 4, 5, etc.

The above procedure does not work anymore. I now get a column that has
formulas in every other row but the formulas reference every odd value
instead of each value. So the column reads 1, 3, 5, 7, 9 etc.

This must be an options issue but I can't seem to locate it.

 

[Jacob Skaria]

If you are looking to autofill the blank rows; try the below

1. Select the data range including blank cells..
2. Press F5. From Goto window>Special> from options select 'Blanks'
3. This will select all blanks. Please note that the activate cell is the
first blank row. Now press = and press UpArrow to the cell just above.
4. Keep the selection and the reference. press Ctrl+ Enter


If this post helps click Yes

 

[Excel Autofill]

I just want to autofill the row with the same data set 1-5 with blank
dilineators beween the numbers instead of having adjacent rows:

Before: After:
1 1
2
3 2
4
5 3

4

5

 

[Jacob Skaria]

With data as below; try the below macro which will insert row in between each
entry..If you are new to macros set the Security level to low/medium in
(Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11.
From menu 'Insert' a module and paste the below code. Save. Get back to
Workbook. Run macro from Tools|Macro|Run <selected macro()>

Col A
1
2
3
4
5

Sub Macro()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
Rows(lngRow).Insert
Next
End Sub

If this post helps click Yes

 

[Excel Autofill]

I don't have much experience with macros. Trying to follow your directions,
I was having trouble saving the macro. Isn't there a simple way to do this
in Excel? The procedure I described used to work very simply.

 

[Dave Peterson]

I don't recall any simple formula (like =A1) that would work that way.

But there are formulas that you can use:

If your data is in A1:A#### (starting in row 1), then put this in B1:
=INDEX(A:A,(ROW()+1)/2)

And use your technique.

 

[Excel Autofill]

With your formula, I get the following:

Original: =INDEX(A:A,(ROW()+1)/2) I want:
1 1 1
2 0
3 3 2
4 0
5 5 3

4

5

I think this must be an options issue, not a formula needed.

 

[Jacob Skaria]

Hi

With your data starting fromw row1 of Col A. use the below formula in B1 and
copy down as required. Try and feedback

=IF(MOD(ROW(),2),INDEX(A:A,(ROW()-1)/2+1),"")