trying to drag 2 cells down at a time returning same cell value and on ?

[zone709]

I hve ts in cell B3 and B4.I need to pull the return in like this a I drag down. If I rag this down holding both cells it will go to =data!d10 =data!d10. I need it to go to =data!d9 =data!d9 and so on 10 11 12. Ned to pull in 2 cells at a time. Any help thanks



=data!d8
=data!d8

 

[orsm6]

are you able to put the formula in same row but in different columns: A1 and B1 as example? might be easier than what you are trying to do
then drag it down

 

[mrshl9898]

All I can think of is with a helper column.

A1 = 9, A2 = 9, A3 = 10, A4 = 10
B1 =INDIRECT("data!D"&A1)

Drag B1 down to B4, then select A1:B4 and drag down.

Might be easier with VBA

Sub twoatatime()


Dim ws As Worksheet
Dim ws2 As Worksheet
Dim LastRow As Long
Dim rownum As Long
Dim rownum2 As Long


Set ws = ActiveSheet
Set ws2 = Sheets("Data")


LastRow = ws2.Cells(ws2.Rows.Count, "D").End(xlUp).Row


rownum2 = 1
rownum = 9
Do Until rownum = LastRow + 1
ws.Cells(rownum2, 1).Value = ws2.Cells(rownum, 4).Value
ws.Cells(rownum2 + 1, 1).Value = ws2.Cells(rownum, 4).Value
rownum = rownum + 1
rownum2 = rownum2 + 2
Loop


End Sub

 

[zone709]

are you able to put the formula in same row but in different columns: A1 and B1 as example? might be easier than what you are trying to do
then drag it down

Not really i am just trying to use to pull in the from the sheet but 2 cells at a time returning same cell 2 at a time. I know there is away to do it but little complicated for me. I have to drag it down 3000 cells. If it was only like 10 cells then i would do it manually.

 

[zone709]

All I can think of is with a helper column.

A1 = 9, A2 = 9, A3 = 10, A4 = 10
B1 =INDIRECT("data!D"&A1)

Drag B1 down to B4, then select A1:B4 and drag down.

Might be easier with VBA

Sub twoatatime()


Dim ws As Worksheet
Dim ws2 As Worksheet
Dim LastRow As Long
Dim rownum As Long
Dim rownum2 As Long


Set ws = ActiveSheet
Set ws2 = Sheets("Data")


LastRow = ws2.Cells(ws2.Rows.Count, "D").End(xlUp).Row


rownum2 = 1
rownum = 9
Do Until rownum = LastRow + 1
ws.Cells(rownum2, 1).Value = ws2.Cells(rownum, 4).Value
ws.Cells(rownum2 + 1, 1).Value = ws2.Cells(rownum, 4).Value
rownum = rownum + 1
rownum2 = rownum2 + 2
Loop


End Sub

Ok ill try this thanks for the reply

 

[zone709]

Figured it out

=IF(INDIRECT("Data!D" & INT((ROW()-3)/2)+8)=0,"",INDIRECT("Data!D" & INT((ROW()-3)/2)+8))

This pulls in on every 2 cells what I needed thanks guys

 

[mrshl9898]

Nice one, was mucking around with Row for a while, didn't think to /2.