"starguy" wrote:
There is another problem which I want to tackle.
I want that students's data be entered in only Main sheet and then it
should automatically be pasted in subject sheets (in subjects which are
in blue columns in Main for each student). Students' data should be
pasted in subject sheets in last empty row.
e.g if a student choses 1.1, 1.2 and 1.3 then it should automatically
be pasted in sheets 1.1, 1.2 and sheet 1.3 in last empty row.

Perhaps this non-array formulas set-up would achieve it ..

Refer sample construct at:
http://cjoint.com/?gutPFjL50M
Starguy_Sample3.xls

In Main,

List across in N5: 1.1, 1.2, 1.3, 2.1 etc
Then in N6, copied across, filled down:
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(N$5,$E6:$L6))))0,ROW(),"")

Then in 1.1,

In D6, copied across to F6, filled down to F15:
=IF(ISERROR(SMALL(OFFSET(Main!$M$6:$M$15,,MATCH($E $3,Main!$N$5:$Z$5,0)),ROW(A1))),"",
INDEX(Main!B$6:B$15,MATCH(SMALL(OFFSET(Main!$M$6:$ M$15,,MATCH($E$3,Main!$N$5:$Z$5,0)),ROW(A1)),
OFFSET(Main!$M$6:$M$15,,MATCH($E$3,Main!$N$5:$Z$5, 0)),0)))

Repeat (or copy paste) the formulas in 1.1 in the other sheets: 1.2, 1.3,
2.1 etc

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