These formulas only work when we assume that 1.1 will always be in col E
and 1.2 in col G of Main sheet.
Infact I enter subject codes of ACCA (which are from 1.1 to 1.3, 2.1 to
2.6 and 3.1 to 3.7) and there is no such way that I could enter them in
a sequence.
e.g if a student choose subjects 2.3, 2.4 and 2.5, I enter them as
follows.
Reg # Name Father (subjects/Sections)
A1106 GGG FAGGG 2.3 2 2.4 1 2.5 1
so in sheet 2.3 at the left of this Reg # (i-e A1106) I want 3 as
section and in sheet 2.4 at the left of Reg # I want 1 and in sheet 2.5
at the left of same Reg # I want 1 as section number of that particular
subject.
I elaborate it more, suppose in sheet 1.2 all students which are pasted
in 1.2 sheet do not necessarity have this subject in Main sheet in 2nd
blue column this might be in 1st blue column for some students. Same is
the case for all subjects that in subject sheets some of students may
have that particular subject in first blue column (col E), some may
have it in second blue column and some may have it in third and some
may have that subject code in fourth blue column.

I think its more elaborative now and will help to determine the exact
formula.

thanks for working Max.

Max Wrote:
One way ..

Try something along these lines

In 1.1,

Placed in C6, array-entered with CSE*:
=INDEX(Main!$F$6:$F$15,MATCH(1,(Main!$B$6:$B$15=D6 )*(Main!$E$6:$E$15=$E$3),0))
C6 is then copied down

*press CTRL+SHIFT+ENTER to confirm the formula
(instead of just pressing ENTER)

Similarly, in 1.2,

Placed in C6, array-entered wih CSE:
=INDEX(Main!$H$6:$H$15,MATCH(1,(Main!$B$6:$B$15=D6 )*(Main!$G$6:$G$15=$E$3),0))
C6 then copied down

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=551813