Reference horizontal data vertically and in sets of 3

[duranimal86]

Hello, I am trying to find a way to reference rows of horizontal data with a formula to pull different columns in sets of 3 rows for each row of data. So each row of data would result in 3 corresponding formula rows with different columns referenced in each of the 3 rows. Then i would like to be able to have that set of 3 pattern copy down for multiple rows of horizontal data. I have been trying to make something work with INDEX and/or OFFSET, but haven't been able to get it to work. Below is example data to try and illustrate what i am looking for.

Horizontal Data to be referenced:

ID1	1A	1B	1C	1D	1E	1F
ID2	2A	2B	2C	2D	2E	2F
ID3	3A	3B	3C	3D	3E	3F
ID4	4A	4B	4C	4D	4E	4F

The result I am looking for:

ID1	1A	(hardkeyed1)	(hardkeyed2)	1D
ID1	1A	1B	1C		1E
ID1	1A	1B	1C		1F
ID2	2A	(hardkeyed1)	(hardkeyed2)	2D
ID2	2A	2B	2C		2E
ID2	2A	2B	2C		2F
ID3	3A	(hardkeyed1)	(hardkeyed2)	3D
ID3	3A	3B	3C		3E
ID3	3A	3B	3C		3F
ID4	4A	(hardkeyed1)	(hardkeyed2)	4D
ID4	4A	4B	4C		4E
ID4	4A	4B	4C		4F

 

[Peter_SSs]

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I'm not exactly sure what the 'hardkeyed' cells are but perhaps you would be manually typing over the "?' results below?

21 07 14.xlsm
	A	B	C	D	E	F	G
1	ID1	1A	1B	1C	1D	1E	1F
2	ID2	2A	2B	2C	2D	2E	2F
3	ID3	3A	3B	3C	3D	3E	3F
4	ID4	4A	4B	4C	4D	4E	4F
5
6	ID1	1A	?	?	1D
7	ID1	1A	1B	1C		1E
8	ID1	1A	1B	1C		1F
9	ID2	2A	?	?	2D
10	ID2	2A	2B	2C		2E
11	ID2	2A	2B	2C		2F
12	ID3	3A	?	?	3D
13	ID3	3A	3B	3C		3E
14	ID3	3A	3B	3C		3F
15	ID4	4A	?	?	4D
16	ID4	4A	4B	4C		4E
17	ID4	4A	4B	4C		4F
duranimal86

Cell Formulas
Range	Formula
A6:B17	A6	=INDEX(A$1:A$4,INT((ROWS(A$6:A6)+2)/3))
C6:D17	C6	=IF(MOD(ROWS(C$6:C6),3)=1,"?",INDEX(C$1:C$4,INT((ROWS(C$6:C6)+2)/3)))
E6:E17	E6	=IF(MOD(ROWS(E$6:E6),3)=1,INDEX(E$1:E$4,INT((ROWS(E$6:E6)+2)/3)),"")
F6:F17	F6	=IF(MOD(ROWS(F$6:F6),3)=1,"",INDEX(F$1:G$4,INT((ROWS(F$6:F6)+2)/3),1+(MOD(ROWS(F$6:F6),3)=0)))

 

[duranimal86]

Wow, that worked perfectly! The "hardkeyed" were for constant numbers that will always be the same, so i was able to enter those in place of the "?" to get exactly what i needed. Thank you!

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.