Formula in first, third and fifth row. How do I copy down with correct references?

[ransom666]

Going to try and explain this. I have a 9 rows. There is a formula in the first, third, and fifth row. The formula is the same, but references a cells in another sheet. For example:

B2 ='MyOtherSheet'!A2
B3 =B2 (this sheet)
B4 =B2 (this sheet)
B5 ='MyOtherSheet'!A3
B6 =B5 (this sheet)
B7 =B5 (this sheet)
B8 ='MyOtherSheet'!A4
B9 =B8 (this sheet)
B10 =B8 (this sheet)

I adjusted the formulas in A2, A5, and A8 by hand to make them show what I need to. I was hoping, at this point, I could copy down and get the same result. Instead, the formulas in those cells copy down like so:

B11 ='MyOtherSheet'!A7
B12 =B11 (this sheet)
B13 =B12 (this sheet)

when I really want it to be:

B11 ='MyOtherSheet'!A5
B12 =B11 (this sheet)
B13 =B11 (this sheet)

It's a total wreck. I looked for a solution for a while today, but I'm not even sure I know what I'm looking for. What do you think?

[Pepe Le Mokko]

In B2 enter

Please Login or Register  to view this content.

and pull down

Adapt range as needed but be sure the INDEX range starts in A1

[Glenn Kennedy]

or, more generically, (use the range of the values to be copied)... but otherwise no better or no worse...

=IFERROR(INDEX(Sheet2!$A$2:$A$11,1+INT((ROWS(B$2:B2)-1)/3)),"")

[ransom666]

Appreciate it Glenn and Pepe. I was way off in figuring this out. I'm going to try both and see if I can't understand what exactly is happening with these formulas.

[Glenn Kennedy]

If you get stuck... ask. Use formulas/formula auditing/evaluate formula to step through what Excel is doing.