sequential lookup??

[ajm]

hi folks, i am working on a course selection workbook and have run into an issue. I have listed the course's core subjects at the top of the tab and these are marked off ( a 1 is placed next to the subject name) when the student completes each subject. in one term, they get to choose four electives from 6 choices. In the Core Subjects table, the four electives are listed as Elective 1, 2, 3, & 4, not by subject name. The six choices are in a separate box below the core subjects. when these electives are completed, a mark (a 1) will be placed next to the particular subject. Once a student has completed their first elective, how do i get the mark the mark (a 1) to appear in the Core Subjects table next to Elective 1, and then also for their second, third, and fourth choices?

So if a student completed Elective B, we would place a 1 adjacent to B in the Electives table. How do i get this to show up adjacent to Elective 1 in the Core Subjects table above? I was thinking of a sequential lookup but that requires the lookup value to be common to both tables.

 

[ajm]

I wasn't thinking about it at all. Was just purely asking to get clear picture. .. Wasn't @petertenthije working?

yes. peter's solution works for what i asked for originally. given the suggestion you made about relating the course to the elective chosen by inputting the elective name, i can see another place in my spreadsheet where i can use something like that. I thought that you might already have a method of doing that.

 

[Zot]

yes. peter's solution works for what i asked for originally. given the suggestion you made about relating the course to the elective chosen by inputting the elective name, i can see another place in my spreadsheet where i can use something like that. I thought that you might already have a method of doing that.

No I don't

 

[KRice]

Here is one more idea if you are willing to introduce a blank cell in B4, perhaps by adding an Electives section heading:

MrExcel_20210317.xlsx
	A	B
1	CORE courses
2	Cool Subject	1
3	Another Cool Subject	1
4	ELECTIVES
5	Elective 1	1
6	Elective 2	1
7	Elective 3	0
8	Elective 4	0
9	Not as Cool Subject
10	Ordinary Subject
11	Elective Level 2
12
13	Electives
14	A
15	B
16	C	1
17	D
18	E	1
19	F
Sheet3

Cell Formulas
Range	Formula
B5:B8	B5	=--(SUM(B$4:B4)<>SUM($B$14:$B$19))

 

[ajm]

Thanks Zot. KRice: i'll have a look at it this morning. Thanks for the suggestion.