dynamic formula needed

  • Thread starter Thread starter starguy
  • Start date Start date
In sheet: 1.1,

Put in C6:
=IF(OR(D6="",$E$3=""),"",
IF(OFFSET(cross,MATCH(D6,matchrow,0),MATCH("P1",matchcol,0))=$E$3,
OFFSET(cross,MATCH(D6,matchrow,0),MATCH("S1",matchcol,0)),
IF(OFFSET(cross,MATCH(D6,matchrow,0),MATCH("P2",matchcol,0))=$E$3,
OFFSET(cross,MATCH(D6,matchrow,0),MATCH("S2",matchcol,0)),
IF(OFFSET(cross,MATCH(D6,matchrow,0),MATCH("P3",matchcol,0))=$E$3,
OFFSET(cross,MATCH(D6,matchrow,0),MATCH("S3",matchcol,0)),
IF(OFFSET(cross,MATCH(D6,matchrow,0),MATCH("P4",matchcol,0))=$E$3,
OFFSET(cross,MATCH(D6,matchrow,0),MATCH("S4",matchcol,0)),"")))))
C6 copied down

[C6:C18's formulas can be entered simultaneously into all other identically
structured sheets via grouping the sheets, as explained in previous response.
But do remember to ungroup the sheets immediately thereafter]

Here's the implemented sample:
http://cjoint.com/?gsplvpiSJd
Starguy_Sample2.xls
 
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.

is there any way?

Regards
 
starguy said:
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.
Click to expand...

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 ..

---
 
thank you Max
can I copy this formula in Main to the right for more subjects lik
2.2, 2.3 and so on and same way in those sheets
 
Thanks for that info. I finally figured it out, someone else told me to
click the 'View this thread' link and that worked for me.

At least they permit/enable file uploading, which certainly facilitates
giving assistance in 9 out of 10 cases around here.
 
Back
Top