G
Guest
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
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