Update cells in new sheets

[caudillo]

Hello.
It's my first time here as a registered user, though I 've been getting help from the site for quite sometime.
I am working on a project for my school and I got stuck, so I am asking for your help.
I have a workbook with four sheets. The first two have some informations regarding my colleagues and the students. On the third sheet I have a drop-down list in cell D10, from which I choose a colleague. After choosing a teacher I get the classes he teaches in (in cells J10-J25) by using a formula and the lessons he teaches in every class (in cells K10-K25) again with formula.
Now the fourth sheet is used as a template and, by using a macro, I can copy it as many times as the lessons that the teacher is teaching and make the sheet tab name same as the name of the lesson.
What I want - and can't seem to get it done due to my poor knowledge of VBA - is to update cell D14 in every newly created copy of the template with the classes that were generated by formula in the third sheet (cells J10-J25).
I would appreciate greatly your help on the issue, since it's a project necessary for my school that would save a lot of working time.
Thank you in advance.

 

[bebo021999]

Attach a mini sheet with 4 sheets (XL2BB - Excel Range to BBCode) or an inmage of the 4 sheets (at least)
We need something to test with.

 

[caudillo]

I will try to upload something in English as soon as possible.
You see, my native language is Greek (and so is my workbook).

 

[caudillo]

I 've finally managed to set up an excel file quite similar to the one I am trying to complete.
I hope it will help you understand what I am asking for.
The sheet INFO has many more values but I couldn't get an xl2bb file with all the information in it.
Finally the fourth sheet includes informations that don't affect the other three, so I thought it would be better off without it.

GRADES.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	CLASS 1	TEACHER 1			CLASS	ABR.	LESSON	TEACHERS	TEACHER 1	CLASS 19	LESSON 120
2	CLASS 2	TEACHER 2		CLASS 1LESSON 1	CLASS 1	LN 1	LESSON 1	TEACHER 10	TEACHER 1	CLASS 39	LESSON 179
3	CLASS 3	TEACHER 3		CLASS 1LESSON 2	CLASS 1	LN 2	LESSON 2	TEACHER 11	TEACHER 1	CLASS 12	LESSON 189
4	CLASS 4	TEACHER 4		CLASS 1LESSON 3	CLASS 1	LN 3	LESSON 3	TEACHER 14	TEACHER 1	CLASS 36	LESSON 18
5	CLASS 5	TEACHER 5		CLASS 1LESSON 4	CLASS 1	LN 4	LESSON 4	TEACHER 49	TEACHER 1	CLASS 14	LESSON 114
6	CLASS 6	TEACHER 6		CLASS 1LESSON 5	CLASS 1	LN 5	LESSON 5	TEACHER 21	TEACHER 1	CLASS 29	LESSON 134
7	CLASS 7	TEACHER 7		CLASS 1LESSON 6	CLASS 1	LN 6	LESSON 6	TEACHER 38	TEACHER 2	CLASS 10	LESSON 203
8	CLASS 8	TEACHER 8		CLASS 1LESSON 7	CLASS 1	LN 7	LESSON 7	TEACHER 36	TEACHER 3	CLASS 32	LESSON 21
9	CLASS 9	TEACHER 9		CLASS 1LESSON 8	CLASS 1	LN 8	LESSON 8	TEACHER 66	TEACHER 3	CLASS 11	LESSON 122
10	CLASS 10	TEACHER 10		CLASS 1LESSON 9	CLASS 1	LN 9	LESSON 9	TEACHER 22	TEACHER 3	CLASS 30	LESSON 115
11	CLASS 11	TEACHER 11		CLASS 1LESSON 10	CLASS 1	L 10	LESSON 10	TEACHER 36	TEACHER 3	CLASS 18	LESSON 24
12	CLASS 12	TEACHER 12		CLASS 1LESSON 11	CLASS 1	L 11	LESSON 11	TEACHER 39	TEACHER 3	CLASS 33	LESSON 202
13	CLASS 13	TEACHER 13		CLASS 1LESSON 12	CLASS 1	L 12	LESSON 12	TEACHER 49	TEACHER 3	CLASS 5	LESSON 78
14	CLASS 14	TEACHER 14		CLASS 1LESSON 13	CLASS 1	L 13	LESSON 13	TEACHER 52	TEACHER 3	CLASS 26	LESSON 126
15	CLASS 15	TEACHER 15		CLASS 1LESSON 14	CLASS 1	L 14	LESSON 14	TEACHER 66	TEACHER 3	CLASS 37	LESSON 10
16	CLASS 16	TEACHER 16		CLASS 1LESSON 15	CLASS 1	L 15	LESSON 15	TEACHER 29	TEACHER 3	CLASS 9	LESSON 31
17	CLASS 17	TEACHER 17		CLASS 1LESSON 16	CLASS 1	L 16	LESSON 16	TEACHER 49	TEACHER 4	CLASS 35	LESSON 155
18	CLASS 18	TEACHER 18		CLASS 1LESSON 17	CLASS 1	L 17	LESSON 17	TEACHER 31	TEACHER 4	CLASS 13	LESSON 100
19	CLASS 19	TEACHER 19		CLASS 2LESSON 1	CLASS 2	LN 1	LESSON 1	TEACHER 40	TEACHER 4	CLASS 27	LESSON 105
20	CLASS 20	TEACHER 20		CLASS 2LESSON 2	CLASS 2	LN 2	LESSON 2	TEACHER 16	TEACHER 4	CLASS 7	LESSON 83
21	CLASS 21	TEACHER 21		CLASS 2LESSON 3	CLASS 2	LN 3	LESSON 3	TEACHER 68	TEACHER 4	CLASS 22	LESSON 140
22	CLASS 22	TEACHER 22		CLASS 2LESSON 4	CLASS 2	LN 4	LESSON 4	TEACHER 14	TEACHER 4	CLASS 41	LESSON 55
23	CLASS 23	TEACHER 23		CLASS 2LESSON 5	CLASS 2	LN 5	LESSON 5	TEACHER 35	TEACHER 5	CLASS 16	LESSON 25
24	CLASS 24	TEACHER 24		CLASS 2LESSON 6	CLASS 2	LN 6	LESSON 6	TEACHER 32	TEACHER 5	CLASS 24	LESSON 180
25	CLASS 25	TEACHER 25		CLASS 2LESSON 7	CLASS 2	LN 7	LESSON 7	TEACHER 60	TEACHER 5	CLASS 42	LESSON 99
26	CLASS 26	TEACHER 26		CLASS 2LESSON 8	CLASS 2	LN 8	LESSON 8	TEACHER 6	TEACHER 5	CLASS 3	LESSON 75
27	CLASS 27	TEACHER 27		CLASS 2LESSON 9	CLASS 2	LN 9	LESSON 9	TEACHER 33	TEACHER 5	CLASS 23	LESSON 47
28	CLASS 28	TEACHER 28		CLASS 2LESSON 10	CLASS 2	L 10	LESSON 10	TEACHER 16	TEACHER 5	CLASS 21	LESSON 116
29	CLASS 29	TEACHER 29		CLASS 2LESSON 11	CLASS 2	L 11	LESSON 11	TEACHER 5	TEACHER 5	CLASS 20	LESSON 158
30	CLASS 30	TEACHER 30		CLASS 2LESSON 12	CLASS 2	L 12	LESSON 12	TEACHER 10	TEACHER 5	CLASS 28	LESSON 56
31	CLASS 31	TEACHER 31		CLASS 2LESSON 13	CLASS 2	L 13	LESSON 13	TEACHER 61	TEACHER 6	CLASS 25	LESSON 182
32	CLASS 32	TEACHER 32		CLASS 2LESSON 14	CLASS 2	L 14	LESSON 14	TEACHER 17	TEACHER 6	CLASS 15	LESSON 80
33	CLASS 33	TEACHER 33		CLASS 2LESSON 15	CLASS 2	L 15	LESSON 15	TEACHER 66	TEACHER 6	CLASS 34	LESSON 108
34	CLASS 34	TEACHER 34		CLASS 2LESSON 16	CLASS 2	L 16	LESSON 16	TEACHER 66	TEACHER 6	CLASS 40	LESSON 74
35	CLASS 35	TEACHER 35		CLASS 2LESSON 17	CLASS 2	L 17	LESSON 17	TEACHER 27	TEACHER 6	CLASS 43	LESSON 167
36	CLASS 36	TEACHER 36		CLASS 3LESSON 17	CLASS 3	L 17	LESSON 17	TEACHER 30	TEACHER 6	CLASS 2	LESSON 159
37	CLASS 37	TEACHER 37		CLASS 3LESSON 5	CLASS 3	LN 5	LESSON 5	TEACHER 45	TEACHER 6	CLASS 31	LESSON 63
38	CLASS 38	TEACHER 38		CLASS 3LESSON 6	CLASS 3	LN 6	LESSON 6	TEACHER 45	TEACHER 6	CLASS 17	LESSON 176
39	CLASS 39	TEACHER 39		CLASS 3LESSON 18	CLASS 3	L 18	LESSON 18	TEACHER 41	TEACHER 6	CLASS 38	LESSON 65
40	CLASS 40	TEACHER 40		CLASS 3LESSON 11	CLASS 3	L 11	LESSON 11	TEACHER 5	TEACHER 7	CLASS 1	LESSON 133
41	CLASS 41	TEACHER 41		CLASS 3LESSON 14	CLASS 3	L 14	LESSON 14	TEACHER 21	TEACHER 7	CLASS 4	LESSON 14
42	CLASS 42	TEACHER 42		CLASS 3LESSON 15	CLASS 3	L 15	LESSON 15	TEACHER 4	TEACHER 7	CLASS 6	LESSON 35
43	CLASS 43	TEACHER 43		CLASS 4LESSON 17	CLASS 4	L 17	LESSON 17	TEACHER 27	TEACHER 7	CLASS 8	LESSON 7
44		TEACHER 44		CLASS 4LESSON 5	CLASS 4	LN 5	LESSON 5	TEACHER 32	TEACHER 7	CLASS 41	LESSON 104
45		TEACHER 45		CLASS 4LESSON 6	CLASS 4	LN 6	LESSON 6	TEACHER 32	TEACHER 7	CLASS 15	LESSON 138
46		TEACHER 46		CLASS 4LESSON 18	CLASS 4	L 18	LESSON 18	TEACHER 19	TEACHER 7	CLASS 10	LESSON 48
47		TEACHER 47		CLASS 4LESSON 11	CLASS 4	L 11	LESSON 11	TEACHER 39	TEACHER 8	CLASS 2	LESSON 76
48		TEACHER 48		CLASS 4LESSON 14	CLASS 4	L 14	LESSON 14	TEACHER 36	TEACHER 8	CLASS 4	LESSON 194
49		TEACHER 49		CLASS 4LESSON 15	CLASS 4	L 15	LESSON 15	TEACHER 63	TEACHER 8	CLASS 42	LESSON 154
50		TEACHER 50		CLASS 5LESSON 17	CLASS 5	L 17	LESSON 17	TEACHER 55	TEACHER 8	CLASS 35	LESSON 81
51		TEACHER 51		CLASS 5LESSON 5	CLASS 5	LN 5	LESSON 5	TEACHER 67	TEACHER 8	CLASS 12	LESSON 123
52		TEACHER 52		CLASS 5LESSON 6	CLASS 5	LN 6	LESSON 6	TEACHER 44	TEACHER 8	CLASS 19	LESSON 73
53		TEACHER 53		CLASS 5LESSON 18	CLASS 5	L 18	LESSON 18	TEACHER 59	TEACHER 9	CLASS 25	LESSON 51
54		TEACHER 54		CLASS 5LESSON 11	CLASS 5	L 11	LESSON 11	TEACHER 8	TEACHER 9	CLASS 39	LESSON 59
55		TEACHER 55		CLASS 5LESSON 14	CLASS 5	L 14	LESSON 14	TEACHER 50	TEACHER 9	CLASS 21	LESSON 95
56		TEACHER 56		CLASS 5LESSON 15	CLASS 5	L 15	LESSON 15	TEACHER 37	TEACHER 9	CLASS 40	LESSON 11
57		TEACHER 57		CLASS 6LESSON 19	CLASS 6	L 19	LESSON 19	TEACHER 26 TEACHER 38	TEACHER 9	CLASS 3	LESSON 149
58		TEACHER 58		CLASS 6LESSON 20	CLASS 6	L 20	LESSON 20	TEACHER 3 TEACHER 49	TEACHER 9	CLASS 5	LESSON 188
59		TEACHER 59		CLASS 6LESSON 21	CLASS 6	L 21	LESSON 21	TEACHER 58 TEACHER 11	TEACHER 9	CLASS 36	LESSON 16
60		TEACHER 60		CLASS 6LESSON 22	CLASS 6	L 22	LESSON 22	TEACHER 7 TEACHER 21	TEACHER 9	CLASS 30	LESSON 200
61		TEACHER 61		CLASS 6LESSON 23	CLASS 6	L 23	LESSON 23	TEACHER 61 TEACHER 10	TEACHER 10	CLASS 11	LESSON 139
62		TEACHER 62		CLASS 6LESSON 24	CLASS 6	L 24	LESSON 24	TEACHER 53 TEACHER 14	TEACHER 10	CLASS 26	LESSON 162
63		TEACHER 63		CLASS 6LESSON 25	CLASS 6	L 25	LESSON 25	TEACHER 54 TEACHER 14	TEACHER 10	CLASS 43	LESSON 121
64		TEACHER 64		CLASS 6LESSON 26	CLASS 6	L 26	LESSON 26	TEACHER 22 TEACHER 11	TEACHER 10	CLASS 22	LESSON 66
65		TEACHER 65		CLASS 6LESSON 27	CLASS 6	L 27	LESSON 27	TEACHER 41 TEACHER 38	TEACHER 10	CLASS 37	LESSON 157
66		TEACHER 66		CLASS 7LESSON 19	CLASS 7	L 19	LESSON 19	TEACHER 20 TEACHER 49	TEACHER 10	CLASS 18	LESSON 161
67		TEACHER 67		CLASS 7LESSON 20	CLASS 7	L 20	LESSON 20	TEACHER 16 TEACHER 21	TEACHER 10	CLASS 31	LESSON 88
68		TEACHER 68		CLASS 7LESSON 21	CLASS 7	L 21	LESSON 21	TEACHER 16 TEACHER 10	TEACHER 11	CLASS 14	LESSON 185
69				CLASS 7LESSON 22	CLASS 7	L 22	LESSON 22	TEACHER 28 TEACHER 21	TEACHER 11	CLASS 29	LESSON 70
70				CLASS 7LESSON 23	CLASS 7	L 23	LESSON 23	TEACHER 48 TEACHER 38	TEACHER 11	CLASS 32	LESSON 183
71				CLASS 7LESSON 24	CLASS 7	L 24	LESSON 24	TEACHER 67 TEACHER 11	TEACHER 11	CLASS 28	LESSON 71
72				CLASS 7LESSON 25	CLASS 7	L 25	LESSON 25	TEACHER 23 TEACHER 49	TEACHER 11	CLASS 1	LESSON 141
73				CLASS 7LESSON 26	CLASS 7	L 26	LESSON 26	TEACHER 31 TEACHER 10	TEACHER 11	CLASS 33	LESSON 164
74				CLASS 7LESSON 27	CLASS 7	L 27	LESSON 27	TEACHER 55 TEACHER 14	TEACHER 11	CLASS 27	LESSON 175
75				CLASS 8LESSON 28	CLASS 8	L 28	LESSON 28	TEACHER 19 TEACHER 49	TEACHER 12	CLASS 38	LESSON 89
76				CLASS 8LESSON 29	CLASS 8	L 29	LESSON 29	TEACHER 47 TEACHER 14	TEACHER 12	CLASS 20	LESSON 107
77				CLASS 8LESSON 30	CLASS 8	L 30	LESSON 30	TEACHER 2 TEACHER 21	TEACHER 12	CLASS 6	LESSON 40
78				CLASS 8LESSON 31	CLASS 8	L 31	LESSON 31	TEACHER 30 TEACHER 11	TEACHER 12	CLASS 9	LESSON 22
79				CLASS 8LESSON 32	CLASS 8	L 32	LESSON 32	TEACHER 5 TEACHER 10	TEACHER 12	CLASS 8	LESSON 111
80				CLASS 8LESSON 33	CLASS 8	L 33	LESSON 33	TEACHER 41 TEACHER 38	TEACHER 12	CLASS 17	LESSON 172
81				CLASS 8LESSON 34	CLASS 8	L 34	LESSON 34	TEACHER 43 TEACHER 21	TEACHER 12	CLASS 13	LESSON 50
82				CLASS 8LESSON 35	CLASS 8	L 35	LESSON 35	TEACHER 12 TEACHER 38	TEACHER 12	CLASS 16	LESSON 197
83				CLASS 8LESSON 36	CLASS 8	L 36	LESSON 36	TEACHER 60 TEACHER 11	TEACHER 12	CLASS 23	LESSON 195
84				CLASS 9LESSON 28	CLASS 9	L 28	LESSON 28	TEACHER 20 TEACHER 49	TEACHER 13	CLASS 7	LESSON 146
85				CLASS 9LESSON 29	CLASS 9	L 29	LESSON 29	TEACHER 30 TEACHER 10	TEACHER 13	CLASS 24	LESSON 101
86				CLASS 9LESSON 30	CLASS 9	L 30	LESSON 30	TEACHER 15 TEACHER 14	TEACHER 13	CLASS 34	LESSON 156
87				CLASS 9LESSON 31	CLASS 9	L 31	LESSON 31	TEACHER 47 TEACHER 49	TEACHER 13	CLASS 41	LESSON 77
88				CLASS 9LESSON 32	CLASS 9	L 32	LESSON 32	TEACHER 50 TEACHER 14	TEACHER 13	CLASS 15	LESSON 102
89				CLASS 9LESSON 33	CLASS 9	L 33	LESSON 33	TEACHER 62 TEACHER 21	TEACHER 14	CLASS 10	LESSON 93
90				CLASS 9LESSON 34	CLASS 9	L 34	LESSON 34	TEACHER 17 TEACHER 11	TEACHER 14	CLASS 2	LESSON 54
91				CLASS 9LESSON 35	CLASS 9	L 35	LESSON 35	TEACHER 64 TEACHER 10	TEACHER 14	CLASS 4	LESSON 205
92				CLASS 9LESSON 36	CLASS 9	L 36	LESSON 36	TEACHER 34 TEACHER 38	TEACHER 14	CLASS 42	LESSON 178
93				CLASS 10LESSON 19	CLASS 10	L 19	LESSON 19	TEACHER 23 TEACHER 21	TEACHER 14	CLASS 35	LESSON 34
94				CLASS 10LESSON 37	CLASS 10	L 37	LESSON 37	TEACHER 61 TEACHER 38	TEACHER 14	CLASS 12	LESSON 30
95				CLASS 10LESSON 38	CLASS 10	L 38	LESSON 38	TEACHER 2 TEACHER 11	TEACHER 14	CLASS 19	LESSON 38
96				CLASS 10LESSON 39	CLASS 10	L 39	LESSON 39	TEACHER 11 TEACHER 49	TEACHER 15	CLASS 25	LESSON 127
97				CLASS 10LESSON 40	CLASS 10	L 40	LESSON 40	TEACHER 57 TEACHER 10	TEACHER 15	CLASS 39	LESSON 42
98				CLASS 10LESSON 41	CLASS 10	L 41	LESSON 41	TEACHER 10 TEACHER 14	TEACHER 16	CLASS 21	LESSON 190
99				CLASS 10LESSON 42	CLASS 10	L 42	LESSON 42	TEACHER 1 TEACHER 21	TEACHER 16	CLASS 40	LESSON 79
100				CLASS 10LESSON 43	CLASS 10	L 43	LESSON 43	TEACHER 31 TEACHER 38	TEACHER 16	CLASS 3	LESSON 109
101				CLASS 10LESSON 44	CLASS 10	L 44	LESSON 44	TEACHER 28 TEACHER 11	TEACHER 16	CLASS 5	LESSON 192
102				CLASS 11LESSON 19	CLASS 11	L 19	LESSON 19	TEACHER 21 TEACHER 49	TEACHER 16	CLASS 36	LESSON 94
103				CLASS 11LESSON 37	CLASS 11	L 37	LESSON 37	TEACHER 30 TEACHER 10	TEACHER 16	CLASS 30	LESSON 64
104				CLASS 11LESSON 38	CLASS 11	L 38	LESSON 38	TEACHER 54 TEACHER 14	TEACHER 16	CLASS 11	LESSON 136
105				CLASS 11LESSON 39	CLASS 11	L 39	LESSON 39	TEACHER 42 TEACHER 49	TEACHER 17	CLASS 26	LESSON 62
106				CLASS 11LESSON 40	CLASS 11	L 40	LESSON 40	TEACHER 32 TEACHER 14	TEACHER 17	CLASS 43	LESSON 36
107				CLASS 11LESSON 41	CLASS 11	L 41	LESSON 41	TEACHER 23 TEACHER 21	TEACHER 17	CLASS 22	LESSON 168
108				CLASS 11LESSON 42	CLASS 11	L 42	LESSON 42	TEACHER 8 TEACHER 11	TEACHER 17	CLASS 37	LESSON 177
109				CLASS 11LESSON 43	CLASS 11	L 43	LESSON 43	TEACHER 24 TEACHER 10	TEACHER 17	CLASS 18	LESSON 1
110				CLASS 11LESSON 44	CLASS 11	L 44	LESSON 44	TEACHER 26 TEACHER 38	TEACHER 17	CLASS 31	LESSON 150
111				CLASS 12LESSON 19	CLASS 12	L 19	LESSON 19	TEACHER 54 TEACHER 49	TEACHER 17	CLASS 14	LESSON 171
112				CLASS 12LESSON 37	CLASS 12	L 37	LESSON 37	TEACHER 34 TEACHER 14	TEACHER 18	CLASS 29	LESSON 27
113				CLASS 12LESSON 38	CLASS 12	L 38	LESSON 38	TEACHER 58 TEACHER 21	TEACHER 18	CLASS 32	LESSON 20
114				CLASS 12LESSON 39	CLASS 12	L 39	LESSON 39	TEACHER 29 TEACHER 11	TEACHER 18	CLASS 28	LESSON 41
115				CLASS 12LESSON 40	CLASS 12	L 40	LESSON 40	TEACHER 23 TEACHER 10	TEACHER 18	CLASS 1	LESSON 85
116				CLASS 12LESSON 41	CLASS 12	L 41	LESSON 41	TEACHER 13 TEACHER 38	TEACHER 18	CLASS 33	LESSON 32
117				CLASS 12LESSON 42	CLASS 12	L 42	LESSON 42	TEACHER 35 TEACHER 21	TEACHER 18	CLASS 27	LESSON 69
118				CLASS 12LESSON 43	CLASS 12	L 43	LESSON 43	TEACHER 24 TEACHER 38	TEACHER 18	CLASS 38	LESSON 33
119				CLASS 12LESSON 44	CLASS 12	L 44	LESSON 44	TEACHER 9 TEACHER 11	TEACHER 19	CLASS 20	LESSON 19
120				CLASS 13LESSON 19	CLASS 13	L 19	LESSON 19	TEACHER 18 TEACHER 49	TEACHER 19	CLASS 6	LESSON 96
121				CLASS 13LESSON 45	CLASS 13	L 45	LESSON 45	TEACHER 24 TEACHER 10	TEACHER 19	CLASS 9	LESSON 130
122				CLASS 13LESSON 46	CLASS 13	L 46	LESSON 46	TEACHER 26 TEACHER 14	TEACHER 19	CLASS 8	LESSON 193
123				CLASS 13LESSON 47	CLASS 13	L 47	LESSON 47	TEACHER 48 TEACHER 49	TEACHER 19	CLASS 17	LESSON 4
124				CLASS 13LESSON 48	CLASS 13	L 48	LESSON 48	TEACHER 58 TEACHER 14	TEACHER 19	CLASS 13	LESSON 166
125				CLASS 13LESSON 49	CLASS 13	L 49	LESSON 49	TEACHER 19 TEACHER 21	TEACHER 19	CLASS 16	LESSON 128
126				CLASS 13LESSON 50	CLASS 13	L 50	LESSON 50	TEACHER 42 TEACHER 11	TEACHER 20	CLASS 23	LESSON 72
127				CLASS 13LESSON 51	CLASS 13	L 51	LESSON 51	TEACHER 11 TEACHER 10	TEACHER 20	CLASS 7	LESSON 160
128				CLASS 13LESSON 52	CLASS 13	L 52	LESSON 52	TEACHER 53 TEACHER 38	TEACHER 20	CLASS 24	LESSON 91
129				CLASS 13LESSON 53	CLASS 13	L 53	LESSON 53	TEACHER 6 TEACHER 49	TEACHER 20	CLASS 34	LESSON 184
130				CLASS 13LESSON 54	CLASS 13	L 54	LESSON 54	TEACHER 26 TEACHER 14	TEACHER 20	CLASS 4	LESSON 191
131				CLASS 13LESSON 55	CLASS 13	L 55	LESSON 55	TEACHER 43 TEACHER 21	TEACHER 20	CLASS 6	LESSON 119
132				CLASS 14LESSON 56	CLASS 14	L 56	LESSON 56	TEACHER 54 TEACHER 11	TEACHER 20	CLASS 8	LESSON 17
133				CLASS 14LESSON 57	CLASS 14	L 57	LESSON 57	TEACHER 59 TEACHER 10	TEACHER 20	CLASS 41	LESSON 199
134				CLASS 14LESSON 58	CLASS 14	L 58	LESSON 58	TEACHER 24 TEACHER 38	TEACHER 20	CLASS 15	LESSON 152
135				CLASS 14LESSON 19	CLASS 14	L 19	LESSON 19	TEACHER 22 TEACHER 49	TEACHER 20	CLASS 10	LESSON 196
136				CLASS 14LESSON 59	CLASS 14	L 59	LESSON 59	TEACHER 62 TEACHER 14	TEACHER 21	CLASS 2	LESSON 147
137				CLASS 14LESSON 60	CLASS 14	L 60	LESSON 60	TEACHER 1 TEACHER 21	TEACHER 21	CLASS 4	LESSON 92
138				CLASS 14LESSON 61	CLASS 14	L 61	LESSON 61	TEACHER 57 TEACHER 11	TEACHER 21	CLASS 42	LESSON 118
139				CLASS 14LESSON 62	CLASS 14	L 62	LESSON 62	TEACHER 25 TEACHER 10	TEACHER 21	CLASS 35	LESSON 144
140				CLASS 14LESSON 63	CLASS 14	L 63	LESSON 63	TEACHER 64 TEACHER 38	TEACHER 21	CLASS 12	LESSON 198
141				CLASS 14LESSON 64	CLASS 14	L 64	LESSON 64	TEACHER 3 TEACHER 38	TEACHER 21	CLASS 19	LESSON 165
142				CLASS 15LESSON 56	CLASS 15	L 56	LESSON 56	TEACHER 57 TEACHER 11	TEACHER 21	CLASS 25	LESSON 201
143				CLASS 15LESSON 57	CLASS 15	L 57	LESSON 57	TEACHER 46 TEACHER 49	TEACHER 21	CLASS 39	LESSON 57
144				CLASS 15LESSON 58	CLASS 15	L 58	LESSON 58	TEACHER 64 TEACHER 10	TEACHER 21	CLASS 21	LESSON 112
145				CLASS 15LESSON 19	CLASS 15	L 19	LESSON 19	TEACHER 67 TEACHER 14	TEACHER 22	CLASS 40	LESSON 67
146				CLASS 15LESSON 59	CLASS 15	L 59	LESSON 59	TEACHER 3 TEACHER 21	TEACHER 22	CLASS 3	LESSON 110
147				CLASS 15LESSON 60	CLASS 15	L 60	LESSON 60	TEACHER 12 TEACHER 38	TEACHER 22	CLASS 5	LESSON 8
148				CLASS 15LESSON 61	CLASS 15	L 61	LESSON 61	TEACHER 48 TEACHER 11	TEACHER 22	CLASS 36	LESSON 3
149				CLASS 15LESSON 62	CLASS 15	L 62	LESSON 62	TEACHER 6 TEACHER 49	TEACHER 22	CLASS 30	LESSON 6
150				CLASS 15LESSON 65	CLASS 15	L 65	LESSON 65	TEACHER 1 TEACHER 10	TEACHER 22	CLASS 11	LESSON 117
INFO

Cell Formulas
Range	Formula
D2:D150	D2	=CONCATENATE(E2,G2)

GRADES.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5
6
7
8
9
10	Choose teacher:			TEACHER 52						CLASS 11	LESSON 109
11										CLASS 26	LESSON 74
12										CLASS 43	LESSON 131
13										CLASS 22	LESSON 73
14										CLASS 37	LESSON 102
15										CLASS 18	LESSON 52
16										CLASS 31	LESSON 52
17										CLASS 14	LESSON 147
18										CLASS 29	LESSON 62
19										CLASS 32	LESSON 137
20										CLASS 28	LESSON 69
21										CLASS 1	LESSON 168
22										CLASS 33	LESSON 5
23
24
25
CHOICE

Cell Formulas
Range	Formula
J10:J25	J10	=IFERROR(INDEX(INFO!$J$1:$J$497,SMALL(IF(CHOICE!$D$10=INFO!$I$1:$I$497,ROW(INFO!$I$1:$I$497)-ROW(INFO!$I$1)+1),ROW(1:1)))," ")
K10:K25	K10	=IFERROR(INDEX(INFO!$K$1:$K$497,SMALL(IF(CHOICE!$D$10=INFO!$I$1:$I$497,ROW(INFO!$I$1:$I$497)-ROW(INFO!$I$1)+1),ROW(1:1)))," ")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
D10:H10	List	=INFO!$B$1:$B$68

GRADES.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4
5
6
7
8
9
10
11
12
13
14	CLASS
15	LESSON			#Δ/Υ								TEMPLATE
16	TEACHERS			#Δ/Υ
17
18
19
TEMPLATE

Cell Formulas
Range	Formula
L15	L15	=MID(CELL("filename",A2),FIND("]",CELL("filename",A2))+1,256)
D15	D15	=VLOOKUP($L$15,INFO!$F$2:$G$375,2,FALSE)
D16	D16	=VLOOKUP($D$14&$D$15,INFO!$D$2:$H$375,5,FALSE)

 

[caudillo]

I forgot to mention that it's a .xlsm file that uses the below code:

Sub CreateSheetsFromList()
    Dim ws As Worksheet, Ct As Long, c As Range
    Set ws = Worksheets("TEMPLATE")
    Application.ScreenUpdating = False
    For Each c In Sheets("CHOICE").Range("K10:K30")
        If c.Value <> "" Then
            ws.Copy after:=Sheets(Sheets.Count)
            ActiveSheet.Name = c.Value
            Ct = Ct + 1
        End If
    Next c
    If Ct > 0 Then
        MsgBox Ct & " new files were created."
    Else
        MsgBox "There are no items on the list."
    End If
    Application.ScreenUpdating = True
End Sub

 

[caudillo]

I would like to metion something about the code I am using.
When new worksheets get created the code stops creating sheets if the teacher is teaching the same lesson in a different class. Is there a way to correct this?
I don't want to put any pressure but it's been a while since I posted my issue and I got no answer. Am I asking for something that can't be done?

 

[caudillo]

Great news! I 've finally managed to update cell D14 by using INDEX and MATCH formula.
The last problem remaining is to fix the code so that when a teacher teaches the same lesson in different classes the code won't stop creating new sheets. The lessons are in the range K10:K30 that is mentioned in the code.
Any help will be greatly appreciated...
P.S.: Another thing I don't understand about the code (I 've got the code from the internet) is the use of "Ct". What does it do?