Reference value from a table and copy to every 3 rows down on another sheet

[LarsAustin]

Hi All,

Greetings from Auckland, New Zealand.

It is my first time to post in this forum. I need the help of excel experts here. I am working on a time series spreadsheet which tracks supply levels of our products.

What I want to do is to get the item codes and item descriptions in Sheet2 and copy it to Column A and Column B in Sheet1 respectively but on every 3 rows after. In cell A1 in Sheet1, I can reference the value in cell A2 in Sheet2 and I can do the same for cell B2 so it will be copied to cell B1 in Sheet1 (which will give me 1221 in cell A1 and ABC in cell B1 in Sheet1). But how can I get the value in cell A3 and B3 from Sheet2 to cell A4 and B4 in Sheet1 and the value in cell A4 and B4 from Sheet2 to cell A7 and B7 in Sheet1?

I have hundreds of products so referencing it manually will be a pain. I want a formula that I can just copy and paste and give me the desired result. I am working on large file so ideally I do not want a volatile function like OFFSET.

Thank you in advance.

Sheet1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]=Sheet2!A2[/TD]
[TD]=Sheet2!B2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item Code[/TD]
[TD]Item Description[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1221[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1222[/TD]
[TD]DEF[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1223[/TD]
[TD]GHI[/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Hi. The volatile function makes no difference if you are going to copy and paste. This formula is dependant on its position in the sheet so has to be started in A1 of Sheet1. Copy down as far as required:

=IF(MOD(ROW(),3)=1,INDIRECT("Sheet2!A"&((ROW()+2)/3)+1),"")

 

[LarsAustin]

That's an awesome solution steve the fish! Absolutely perfect.

Cheers!