Using = to reference another tab

[mickyd67]

Hi All,

I hope I explain this one correctly.

I have Tab 1 with 100 item codes in Column A - running A1 to A100

In Tab 2 I want to reference these item codes, but I want to do that with other data - so it's every 5 rows I want the reference (i.e. item code) to be visible in, and then I'll merge / centre the data.

How do I do that? Is it Offset, or something? I can't figure it out.

Thanks,

 

[6StringJazzer]

You have described what your data look likes like in "Tab 1", but you have given no clue at to what your result should look like in "Tab 2". Also it looks like your item codes are in column B, not A.

Assuming you want consecutive rows on "Tab 2" referencing the items, then you want this for the first data row on "Tab 2" and copy down.

=INDEX('Tab 1'!A:A,(ROW(A1)-1)*4+1)

Result will look like this:

$scratch.xlsm
	A
1	Items
2	100001111
3	100002222
4	100003333
Sheet7

 

[mickyd67]

Thanks for the reply.

I’m showing Tab 2 in that image. That’s what the 2nd tab layout looks like - column B is referencing column A1, A2 etc

 

[6StringJazzer]

Oh, so the situation is backwards.

=INDEX('Tab 1'!A:A,((ROW()-1)/4)+1)

 

[mickyd67]

Great, thanks for your help. I'll give that a try.