Excel 2007 : Reference every other row on a different sheet

**dancing-shadow** · 07-19-2010, 06:38 AM

I have 2 sheets, on the 1st sheet, I want to add in a fomula and be able to drag down to reference the 2nd sheet, and every other row.

So I want the formula's to do this...

sheet1, cell A1, reference sheet2 cell A1
sheet1, cell A2, reference sheet 2 cell A3
sheet1, cell A3, reference sheet 2 cell A5
sheet1, cell A4, reference sheet 2 cell A7....

etc - so I can pull it down and it will continue to reference every other row as above.

Any help would be greatly appreciated - I've spent all morning searching around for ways to do it, but none seem quite right!!

**DonkeyOte** · 07-19-2010, 06:50 AM

Use INDEX

Sheet1!A1
=INDEX(Sheet2!A:A,(2*ROWS(A$1:A1))-1)
copied down

**dancing-shadow** · 07-19-2010, 07:06 AM

This seems to work fine, however I need it to start a few lines down... (I thought this would be a simple case of amending the row/column labels in the fomula, but I can't work that out either...)

so the cell where the formula needs to start on sheet1 is B127, and it needs to reference sheet2, C6

Could you please explain the INDEX formula you provided, so I can edit it accordingly?

Thanks

**DonkeyOte** · 07-19-2010, 07:21 AM

Originally Posted by dancing-shadow

so the cell where the formula needs to start on sheet1 is B127, and it needs to reference sheet2, C6

Perhaps simpler to follow if you revert to:

Sheet1!B127:
=INDEX(Sheet2!C:C,6+2*(ROWS(B$127:B127)-1))

So the increment from row 6 will be 0,2,4,6 etc as the formula is copied down.