Referencing repeating non-consecutive rows
 

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


--
caraj
------------------------------------------------------------------------
caraj's Profile: http://www.excelforum.com/member.php...o&userid=33718
View this thread: http://www.excelforum.com/showthread...hreadid=535003

Referencing repeating non-consecutive rows
 

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.


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=535003

Referencing repeating non-consecutive rows
 

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

--
HTH...

Jim Thomlinson


"Ikaabod" wrote:


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.


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=535003