C
Chris m=ec
I'm trying again. I posted this problem previously under "Two different
criterias in one formula?" So I'm going try to explain it again.
Sheet 1 is a huge time-table/subject roster. Sheet 2 is a reference for me
to double-check if the correct subject had been assigned to the appropriate
trade group.
1. In sheet 1 col C (labelled "Group") is the different trade groups in
each row, col D (labelled "Subjects") the appropriate subject in the
appropriate row for each group in col C, col E (labelled "Venue"), col F
(labelled "Lecturer"). This whole range repeats from col G onwards and again
from col K and so on, and it carries on for 2nd years, 3rd years and 4th
years. If the problem can be solved for the 1st years, it's just a matter of
copying the formula to the next intake group.
2. Sheet 2 Cell B1:K1 are the different trade groups and Cell A2:A30 the
different subjects. The first problem is that I don't work with numerical
values in both sheets but with text. Sheet 2 must be marked with an "x" in
each corresponding cell if the appropriate subject had been assigned to the
appropriate group. Example:
B1 are labelled "L1BM"
A2 is "Engineering Science".
If "Engineering Science" is assigned to "L1BM" somewhere in sheet 1, then an
"x" must show up in sheet 2 cell B2.
Basically Excel must lookup "L1BM" in sheet 1, see if "Engineering Science"
is assigned to the adjacent cell and return with "x" in sheet 2.
Unfortunately SUMIF only works with numerical during summing, or am I wrong?
Thanks for giving this problem attention.
criterias in one formula?" So I'm going try to explain it again.
Sheet 1 is a huge time-table/subject roster. Sheet 2 is a reference for me
to double-check if the correct subject had been assigned to the appropriate
trade group.
1. In sheet 1 col C (labelled "Group") is the different trade groups in
each row, col D (labelled "Subjects") the appropriate subject in the
appropriate row for each group in col C, col E (labelled "Venue"), col F
(labelled "Lecturer"). This whole range repeats from col G onwards and again
from col K and so on, and it carries on for 2nd years, 3rd years and 4th
years. If the problem can be solved for the 1st years, it's just a matter of
copying the formula to the next intake group.
2. Sheet 2 Cell B1:K1 are the different trade groups and Cell A2:A30 the
different subjects. The first problem is that I don't work with numerical
values in both sheets but with text. Sheet 2 must be marked with an "x" in
each corresponding cell if the appropriate subject had been assigned to the
appropriate group. Example:
B1 are labelled "L1BM"
A2 is "Engineering Science".
If "Engineering Science" is assigned to "L1BM" somewhere in sheet 1, then an
"x" must show up in sheet 2 cell B2.
Basically Excel must lookup "L1BM" in sheet 1, see if "Engineering Science"
is assigned to the adjacent cell and return with "x" in sheet 2.
Unfortunately SUMIF only works with numerical during summing, or am I wrong?
Thanks for giving this problem attention.