Indirect reference with changing cell value?

[GolfHelp]

Good Evening,

I'm trying to drag/copy-paste an indirect formula where the row value in the reference changes. Then continue the pattern over hundreds of cells. This is the general idea:

=INDIRECT("Sheet1!A3")
=INDIRECT("Sheet1!C3")
=INDIRECT("Sheet1!E3")
=INDIRECT("Sheet1!H3")
=INDIRECT("Sheet1!I3")
=INDIRECT("Sheet1!A11")
=INDIRECT("Sheet1!C11")
=INDIRECT("Sheet1!E11")
=INDIRECT("Sheet1!H11")
=INDIRECT("Sheet1!I11")

So on, so on. So, I need the column to change 5 times within the same row, then the row to change by 8 and have that repeat. Currently I'm manually changing the Cell and it's becoming exhausting as the sheet grows. I thought I could do this with OFFSET but I'm having issues figuring out how.

 

[awoohaw]

just how many indirect functions will you have? and how many other formulas will rely on these cells?
INDIRECT (and OFFSET) are volatile functions which can drain the processing of workbook.

can you give the total dimensions of the data in sheet1?

 

[awoohaw]

And is there some kind of reason why the sequence of Columns is not consistent:
A, C, E, H, I are column numbers: 1,3,5,8,9.

 

[awoohaw]

Do you want a matrix or just one column?

 

[GolfHelp]

Sorry, that was a typo, columns 1,3,5,7,9 (A, C, E, G, I)

I want it all in one column.

I understand the formulas are cumbersome but I don’t know how else to do this. It will end up being about 1,500 cells that I put this formula in.

 

[GolfHelp]

Do you want a matrix or just one column?

Sorry, that was a typo, columns 1,3,5,7,9 (A, C, E, G, I)

I want it all in one column.

I understand the formulas are cumbersome but I don’t know how else to do this. It will end up being about 1,500 cells that I put this formula in.

 

[awoohaw]

For a column of all the number paste this into cell A1 and copy down. If you paste in a different row, you must adjust the subtraction number to equal the row number:

=INDEX(Golfhelp1!$A$1:$CV$102,(((ROUNDDOWN((ROW(A1)-1)/5,0))*8)+3),CHOOSE(MOD((ROW(A1)-1),5)+1,1,3,5,8,9))

 

[awoohaw]

Here are miniworkbooks:

Golfhelp1 is your sheet1:

mr excel questions 22.xlsm
	A	B	C	D	E	F	G	H	I
1	70	87	89	87	88	56	51	89	64
2	81	72	57	75	77	68	79	75	54
3	1	274	2	233	3	159	154	4	5
4	111	242	123	257	543	183	4	54	566
5	253	108	264	279	118	118	75	39	133
6	247	284	186	282	257	278	263	196	275
7	88	290	298	148	75	113	49	197	254
8	214	202	137	204	125	148	283	66	147
9	299	287	44	187	46	67	232	214	234
10	246	147	42	215	288	205	211	201	107
11	6	242	7	257	8	183	64	9	10
12	235	79	159	244	275	298	292	165	219
13	110	42	290	166	146	167	235	83	46
14	55	123	120	193	224	84	272	256	48
15	254	214	52	144	141	89	126	277	43
16	47	248	36	94	119	68	235	269	212
17	70	93	298	246	99	216	157	96	273
18	50	78	203	291	297	215	106	138	79
19	159	99	89	201	41	281	74	120	273
20	178	160	191	243	125	148	171	261	180
21	72	285	241	92	295	142	82	293	204
22	285	184	276	93	62	55	114	247	158
23	288	102	136	87	256	170	288	217	292
24	84	113	141	222	83	183	280	90	162
25	62	257	257	237	269	277	260	281	229
26	121	229	159	109	298	130	248	239	177
27	198	46	249	43	146	300	235	213	127
28	137	44	174	226	36	184	230	94	132
29	134	288	192	94	275	297	45	40	248
30	120	124	216	30	132	232	79	51	288
31	215	181	100	108	159	299	84	125	241
32	187	149	61	120	180	59	224	99	178
33	245	286	190	174	154	127	280	27	190
34	48	200	281	38	256	166	134	74	61
35	201	224	147	287	196	99	299	41	186
36	219	71	45	123	144	47	126	80	127
37	211	135	174	87	30	219	103	260	45
38	58	153	158	27	290	27	82	141	254
39	102	239	117	94	235	253	289	298	249
40	154	74	281	31	133	65	164	100	54
41	192	284	243	150	58	234	144	270	224
42	42	253	286	165	141	128	100	275	117
43	242	292	250	279	236	206	133	297	289
44	263	182	233	153	237	198	89	76	225
45	59	91	91	32	76	143	115	161	216
46	183	91	251	282	30	124	287	151	196
47	282	224	90	35	260	126	72	176	291
48	115	39	135	262	268	228	192	254	183
49	255	194	185	202	238	201	102	300	87
50	91	196	97	188	231	204	211	161	56
51	162	189	140	95	240	289	298	209	33
52	43	251	71	180	78	54	270	248	207
53	155	69	191	171	36	63	146	223	297
54	33	113	284	230	156	123	223	57	143
Golfhelp1

Golfhelp2 is your formula sheet:
This is using the choose function instead of just updating a mod formul (because of the G/H mix up). But it was a quick adjustment:

mr excel questions 22.xlsm
	A
1	1
2	2
3	3
4	154
5	5
6	6
7	7
8	8
9	64
10	10
11	159
Golfhelp2

Cell Formulas
Range	Formula
A1:A11	A1	=INDEX(Golfhelp1!$A$1:$I$54,(((ROUNDDOWN((ROW(A1)-1)/5,0))*8)+3),CHOOSE(MOD((ROW(A1)-1),5)+1,1,3,5,7,9))

 

[awoohaw]

Here is the address of the target for each cell's formula

mr excel questions 22.xlsm
	A	B
1	1	Golfhelp1!A3
2	2	Golfhelp1!C3
3	3	Golfhelp1!E3
4	154	Golfhelp1!G3
5	5	Golfhelp1!I3
6	6	Golfhelp1!A11
7	7	Golfhelp1!C11
8	8	Golfhelp1!E11
9	64	Golfhelp1!G11
10	10	Golfhelp1!I11
11	159	Golfhelp1!A19
Golfhelp2

Cell Formulas
Range	Formula
A1:A11	A1	=INDEX(Golfhelp1!$A$1:$I$54,(((ROUNDDOWN((ROW(A1)-1)/5,0))*8)+3),CHOOSE(MOD((ROW(A1)-1),5)+1,1,3,5,7,9))
B1:B11	B1	=ADDRESS(((ROUNDDOWN((ROW(A1)-1)/5,0))*8)+3,CHOOSE(MOD((ROW(A1)-1),5)+1,1,3,5,7,9),4,1,"Golfhelp1")

 

[awoohaw]

INDEX is not volatile, but it does recalculate every time you open the workbook.
The OFFSET function using array formulas in a LET statement could work but I am not that proficient at that.
I am a fan of INDEX.