Offset formula (I think) help needed

[ScottNoddin8]

Hi everyone,

I am having trouble setting up some relative cell references. I have two spreadsheets. One has a list of names on it (from A1 - A50) and the other sheet (Sheet2) is tracking statistics for 12 different stats for each name. The names on Sheet 1 are constantly changing so I need to come up with a formula for Sheet 2 that I can copy down so the first name shows up 12 times and then moves onto the next cell. I have tried the offset formula with not much success.

Basically in this situation A2 through A13 (A1 is the header) on Sheet 2 should equal A2 on Sheet 1 and A14 through A25 should equal A3 on Sheet 1, etc.

It seems like it should be simple but I am struggling to come up with something. I appreciate the help!

 

[KRice]

For Sheet1 that looks like this:

Book1
	A
1	Name1
2	Name2
3	Name3
4	Name4
5	Name5
6	Name6
7	Name7
8	Name8
9	Name9
10	Name10
11	Name11
12	Name12
13	Name13
14	Name14
15	Name15
16	Name16
17	Name17
18	Name18
19	Name19
20	Name20
Sheet1

Is this what you want for Sheet2?

Book1
	A
1	Name1
2	Name1
3	Name1
4	Name1
5	Name1
6	Name1
7	Name1
8	Name1
9	Name1
10	Name1
11	Name1
12	Name1
13	Name2
14	Name2
15	Name2
16	Name2
17	Name2
18	Name2
19	Name2
20	Name2
21	Name2
22	Name2
23	Name2
24	Name2
25	Name3
26	Name3
Sheet2

Cell Formulas
Range	Formula
A1:A240	A1	=INDEX(Sheet1!A1:A20,SEQUENCE(12*COUNTA(Sheet1!A1:A20),,,1/12))
Dynamic array formulas.

The formula will "spill" down to populate 12 times for each name.

 

[kweaver]

Will this do what you want?

021623 Misc.xlsx
	A	B	C	D
1	Names			Repeat
2	John	John		3
3	Mary	John
4	Bill	John
5	William	Mary
6	Jackson	Mary
7	Susanne	Mary
8		Bill
9		Bill
10		Bill
11		William
12		William
13		William
14		Jackson
15		Jackson
16		Jackson
17		Susanne
18		Susanne
19		Susanne
Sheet21

Cell Formulas
Range	Formula
B2:B19	B2	=LET(in,A2:A7,rep,D2,s,SEQUENCE(ROWS(in)*rep,1,0),INDEX(in,QUOTIENT(s,rep)+1))
Dynamic array formulas.

 

[Fluff]

Another option

Fluff.xlsm
	A
1
2	Name 1
3	Name 2
4	Name 3
5	Name 4
6	Name 5
7	Name 6
8	Name 7
9	Name 8
Sheet1

Fluff.xlsm
	A
1
2	Name 1
3	Name 1
4	Name 1
5	Name 1
6	Name 1
7	Name 1
8	Name 1
9	Name 1
10	Name 1
11	Name 1
12	Name 1
13	Name 1
14	Name 2
15	Name 2
16	Name 2
17	Name 2
18	Name 2
19	Name 2
20	Name 2
21	Name 2
22	Name 2
23	Name 2
24	Name 2
25	Name 2
26	Name 3
27	Name 3
28	Name 3
29	Name 3
30	Name 3
31	Name 3
32	Name 3
33	Name 3
34	Name 3
35	Name 3
36	Name 3
37	Name 3
38	Name 4
Sheet2

Cell Formulas
Range	Formula
A2:A97	A2	=TOCOL(IF(SEQUENCE(,12),Sheet1!A2:A9))
Dynamic array formulas.