Referencing repeating non-consecutive rows

[caraj]

When one references another worksheet i.e.
Sheet1 A1 = Sheet2!A1
when one copies the reference to Sheet1 A2 sas automatically
increments by 1 giving
Sheet1 A2 = Sheet2!A2

How does one make excel skip a row:

I want 2 lines copied consecutively then 1 line skipped automatically.

Using the above notation I am trying to get
Sheet1 A1 = Sheet2!A1
Sheet1 A2 = Sheet2!A2
Sheet1 A3 no mapping
Sheet1 A4 = Sheet2!A3
Sheet1 A5 = Sheet2!A4
Sheet1 A6 no mapping and so on for a few
thousand rows.

Thanks In Advance,
John

 

[Ikaabod]

One way, if you're willing to add a new column, is to simply put 0 for
B1:B3 then make B4 formula =B1+1 and copy B4 down as far as needed.

Then in A1 formula =OFFSET(Sheet2!A1,-B1,0)
in A2 formula =OFFSET(Sheet2!A2,-B2,0)
leave A3 blank
copy A1:A3 down as far as needed.

 

[Guest]

With offset being a volatile function this will degrade the perfomance
(possibly substantially since there will be thousands of these). Volatile
means that the function will recalculate every time a calculation runs, not
just when one of it's dependant cells change. This can be a big drain on
resources. If it was me I would be more inclined to use a macro to just
insert blank rows every two rows.

Sub InsertLines()
Dim wks As Worksheet
Dim rng As Range
Set wks = ActiveSheet

With wks
Set rng = .Range("A3")
Do While Not IsEmpty(rng.Value)
rng.EntireRow.Insert
Set rng = rng.Offset(2, 0)
Loop
End With
End Sub