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.

 

[Zot]

You meant something like this?

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

 

[petertenthije]

Not sure I quite understand the question. Would this cover your requirement?

Using Zot's table as example:
B4 formula: =if(sum(B13:B18)=1,1,0)
B5 formula: =if(sum(B13:B18)=2,1,0)
B6 formula: =if(sum(B13:B18)=3,1,0)
B7 formula: =if(sum(B13:B18)=4,1,0)

 

[Zot]

Not sure I quite understand the question. Would this cover your requirement?

Using Zot's table as example:
B4 formula: =if(sum(B13:B18)=1,1,0)
B5 formula: =if(sum(B13:B18)=2,1,0)
B6 formula: =if(sum(B13:B18)=3,1,0)
B7 formula: =if(sum(B13:B18)=4,1,0)

I was just guessing since it was not clear. Putting B in Core Courses is the way to relate the Core Courses table to Elective Table

 

[ajm]

Not sure I quite understand the question. Would this cover your requirement?

Using Zot's table as example:
B4 formula: =if(sum(B13:B18)=1,1,0)
B5 formula: =if(sum(B13:B18)=2,1,0)
B6 formula: =if(sum(B13:B18)=3,1,0)
B7 formula: =if(sum(B13:B18)=4,1,0)

Peter, this is almost what i want. using these formulas works when there is only one elective selected. if two electives are selected, the mark only shows against the second Elective 2 in the Core Subjects table, as the sum (in the electives table) is now two.

then when 2 electives are marked:

Thank you though. your answer gave me an idea. I have used the following formulas in conjunction with a simple sum formula in C20, under the electives table. Seems to work.

Thanks Peter.

 

[ajm]

I was just guessing since it was not clear. Putting B in Core Courses is the way to relate the Core Courses table to Elective Table

Zot, you get a gong too. in another column, i actually need to identify which Elective subject was selected so will be using what you sent through also.

Many thanks.

 

[Zot]

Please use XL2BB in future so that we can just copy paste your sheet instead of recreated from picture.

 

[Zot]

Zot, you get a gong too. in another column, i actually need to identify which Elective subject was selected so will be using what you sent through also.

Many thanks.

Yes. I don't see how to relate between table

 

[ajm]

You meant something like this?

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

Did you have a formula that did this?

 

[Zot]

Did you have a formula that did this?

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