Row jumping formula?

[GD]

Hi, reasonably simple one...I have some data in cells within one column but
separated by rows each time, so for example the figures are in BI5, BI8,
BI11, BI12 etc

On another worksheet I want to condense this data - so essentially get rid
of the gaps in between so it would read BI5, BI6, BI7 etc - the sample size
is so large removing rows would be extremely time consuming, all i want
really is a formula that references the figure, and as I copy it down in the
new column it will reference every 3rd cell in the range

 

[Jarek Kujawa]

If i didn't miss anything
on another worksheet in A1

=OFFSET(Sheet1!$A$5,3*(ROW()-1),)

then copy/drag down

 

[Jarek Kujawa]

If i didn't miss anything
on another worksheet in A1

=OFFSET(Sheet1!$A$5,3*(ROW()-1),)

then copy/drag down

 

[Jacob Skaria]

Try the below and then you can work it out...(every 3rd cell)

Enter few values in A5, A8, A11, A14 etc;
In cell B5 enter the formula =A5

In B6 enter the below formula and copy down
= INDIRECT("A"&ROW($B$5)+(ROW()-ROW($B$5))*3)

 

[Jacob Skaria]

Try the below and then you can work it out...(every 3rd cell)

Enter few values in A5, A8, A11, A14 etc;
In cell B5 enter the formula =A5

In B6 enter the below formula and copy down
= INDIRECT("A"&ROW($B$5)+(ROW()-ROW($B$5))*3)

 

[Eduardo]

Hi,
You can easily remove the blank cells with a macro

Sub Macro1()
'
' Macro1 Macro
'

Dim myrange, MyRange1 As Range
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Sheets("Sheet1").Range("A1:BI" & lastRow)
If Range("A1") = "" Then

myrange.Delete
End If



End Sub

 

[Eduardo]

Hi,
You can easily remove the blank cells with a macro

Sub Macro1()
'
' Macro1 Macro
'

Dim myrange, MyRange1 As Range
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Sheets("Sheet1").Range("A1:BI" & lastRow)
If Range("A1") = "" Then

myrange.Delete
End If



End Sub

 

[Gary''s Student]

In Sheet1 we have data in B5,B8,B11,B14,B17,...

In Sheet2, pick a cell and enter:

=INDIRECT("Sheet1!B" & 3*ROWS(A1:$A$1)+2) and copy down

 

[Gary''s Student]

In Sheet1 we have data in B5,B8,B11,B14,B17,...

In Sheet2, pick a cell and enter:

=INDIRECT("Sheet1!B" & 3*ROWS(A1:$A$1)+2) and copy down