Two different criterias in one formula?

[Chris m=ec]

A time-table consists of 6 different groups and 5 different subjects. To
verify the correct subjects for each group, I must use a formula for the
following:

See if group AB in the range C11:J42 has Engineering Science.
See if group DC in the same range has Mathematics.
See if group EF in the same range has Industrial Electronics N1.

I tried the IF function but no go.

 

[Chris m=ec]

Just to clear up some uncertainty - below is an abbreviated range of the
actual worksheet (Sheet 1). On a seperate worksheet (Sheet 2) with all
subjects and groups for cross reference, I tried formulas
(IF,AND,SUMIF,AND,OR, etc.) to mark with an "x" which subjects had been
assigned to which group. For example:

=IF(AND(B20:E24="L2DM",B20:E24="Eng. Drawing"),"x",""))

This is a HUGE worksheet (1000+ students) and drawing up this time-table to
mark on a seperate worksheet (Sheet 2) if Mathematics has been assigned to
group L2DM, got me completely baffled. Some cells are merged as well (in
case where one subject has to be assigned to two groups).

A B C D
E
19 Time Group Subject Group Subject
20 07:45 L2BM Eng. Science L2FT Mathematics
21 L2DM Motor Theory L2EL Ind. Electronics
22
23 08:45 L2BM Platers' Theory L2FT F & M Theory
24 L2DM Eng. Drawing L1EL Mathematics

Somehow I don't see the light at the end of the curved tunnel!

Thanx

 

[David Biddulph]

If cells have been merged, the recommendation would be to throw the problem
back at whichever bright spark did such a stupid thing to the worksheet.
Merging is very rarely the sensible solution.

 

[Chris m=ec]

Dear Sir,

Thank you kindly for replying,

I am the bright spark that did "such a stupid a thing to the worksheet"
because the function is there to be used if needed. This is a discussion
group to help each person in need. Rather evaluate the problem instead of
the person! Don't let merging blinds you.

The real problem is, what formula or combination of formula's can be used to
show to me if a subject has been already assigned to a specific classgroup in
a range as per example.

Thanx again

 

[David Biddulph]

You are welcome to use merging if it suits you. The advice from many
experts in this newsgroup (and elsewhere) is to avoid it if at all possible.
Good luck with your problem.

 

[Chris m=ec]

Thanx again for replying.

I used merging a lot of times the past 12 years with Excel and didn't run
into unsolvable problems. I took your advise and unmerge but still it
doesn't solve my nightmare, so I'm going to post this problem again.