Make a workload of a particular teacher

[pipsmultan]

Help Required

My problem is i want to past the complete cell of that particular teacher with (subject,

name of that teacher & Class) in there receptive ROW, it will help me to know which

subject & Teacher and which class that particular teacher is teaching

in "Sheet 1" i have highlighted for you with different color of the particular Teachers

teaching i different classes in different periods, like Yellow color is showing "Sir

William" is teaching Math in class 10th - East, and so on

In Sheet 2 if i enter Name of any particular Teacher (Manually/Drop down list) it copy

(subject, name of that teacher & Class) in there receptive ROW

I have attach sample copy link so that i can make my problem clearly understandable to

you

Teacher workload


sorry forgot to mention this if there is an conflict cells in same column "Sheet 1" than

some warring message or any other thing (sign) should appear so that conflicting column

Cells with same name should be eliminated or change

like u can see In (Sheet 1) "Column H9 & H13"
--------------------------
Electric Wiring
Sir James
10th (- West)


E-W
Sir James
9th - West"
-------------------------

" Sir James" teaching two classes at a same time

SO Now if a school principal don't want to change or eliminate the conflict cell and

wants "Sir James" to teach two classes at same time/period than it should be adjusted in

same Cell Row

is it possible & i hope i making my self understandable

Waiting to receive a solution soon.

 

[oldbrewer]

make up a few rows of data as if you only had 3 teachers and show us what you want as output

 

[pipsmultan]

i have past image link of my excel file

http://img42.com/25Uwx

plz check it

thnks

 

[pipsmultan]

here is the link of my excel file



Download-Link:
ExpireBox</pre>
your kind help will be highly appreciated</pre>

 

[oldbrewer]

if you cannot make a sample spreadsheet and explain what you want then i cannot help you - i do not open or download links

or explain it in words more simply - a timetable is not that complicated - teacher cannnot teach maths to one class and english to another at the same time - this needs to highlight when the timetable is being produced.....

 

[pipsmultan]

This Sheet 1 (With Column A,B,C,D,E......) & ROW(4,5,6.......)

A B C D E
PERIODS &
CLASSES "1st Period 7: 00 to 07:35" "2nd Period 07:35 to 08:10" " 3rd Period 08:10to 08:45" "4th Period 08:45to 09:20"

4 10th (East)

Maths Sir William 10th - East

Bio Mis Emily 10th - East

Chem MIs Lily 10th - East

French Sir William 10th-East

<tbody>
</tbody>

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

5 10th (West)

Maths Sir William 10th - West

Bio Mis James 10th - West

Chem Mis Lily 10th-West

Science MIs Lily 10th - West

<tbody>
</tbody>

This Sheet 2 (With Column A,B,C,D,E......) & ROW(4,5,6.......)


A B C D E
PERIODS &
CLASSES "1st Period 7: 00 to 07:35" "2nd Period 07:35 to 08:10" " 3rd Period 08:10to 08:45" "4th Period 08:45to 09:20"

4 Sir William

Maths Sir William 10th - East Maths Sir William 10th - West

French Sir William 10th-East

<tbody>
</tbody>

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

5 Mis Lily

Chem Mis Lily 10th-East Biology Mis Lily 10th-West

Science MIs Lily 9th - West

<tbody>
</tbody>

My big problem is i want to past the complete cell from “sheet 1” of a particular teacher with (subject, name of that teacher & Class) on Sheet 2 horizontal way in there receptive ROW of “Sheet 2”, as you can see above example it will help me to create a workload and to know which subject & Teacher and which class that particular teacher is teaching.


2. Sometimes what happens is if there is a shortage of teachers in our school we merge some couple of teachers in same class OR same period

like u can see In (Sheet:Timetable) "Column B4 & D5"
--------------------------
Maths
Sir William
10th (- East)


Maths
Sir William
10th (- West)

and like (Mis Lily) teaching Chem & Biology in 10th (east & west) by merging the class
Cell D4 & D5 to
-------------------------

" Sir William" teaching two classes at a same time and "Mis Lily" teaching 2/3 classes at same time and soon

Is it possible that these (conflicting) cell also adjust in there respective row in same cell, this will help me to track which teachers are teaching 2/3 classes at same time

and if by mistake i put 2 teacher in same column in different class than any warring message or any other thing (message "Conflicting Entry" or cell get "Highlighted") or any other way around you suggest, this will help me easily eliminated or change Conflicting Entry.


I hope this explain in words more simply



your kind help will be highly appreciated</pre>

 

[oldbrewer]

				CLASS TIMETABLE
			lesson1	lesson2	lesson3	lesson4	lesson5	lesson6	lesson7
		class1	english	maths	history	french	geography	sport	science
		class2	history	french	geography	sport	science	english	maths
		class3	sport	science	english	maths	history	french	geography
				TEACHER ASSIGNMENT
			lesson1	lesson2	lesson3	lesson4	lesson5	lesson6	lesson7
		class1	T1	T2	T3	T1	T4	T5	T6
		class2	T7	T8	T4	T3	T1	T8	T2
		class3	T8	T4	T1	T2	T1	T4	T5
				CONFLICT ANALYSIS
			lesson1	lesson2	lesson3	lesson4	lesson5	lesson6	lesson7
		T1	1	0	1	1	2	0	0
		T2	0	1	0	1	0	0	1
		T3	0	0	1	1	0	0	0
		T4	0	1	1	0	1	1	0
		T5	0	0	0	0	0	1	1
		T6	0	0	0	0	0	0	1
		T7	1	0	0	0	0	0	0
		T8	1	1	0	0	0	1	0
		the cell containing the 2 (teacher 1 lesson 6)
		can be highlighted in red to show conflict

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[pipsmultan]

thnks for help and guiding

i want little bit help in this formula


=IF(COUNTIF('Sheet 1'!B:B,"*"&$A7&"*")>1,"Conflicting Entry",IFERROR(INDEX('Sheet 1'!B:B,MATCH("*"&$A7&"*",'Sheet 1'!B:B,FALSE)),"Off"))



when i copy the formula across and down in "Sheet 2" (A10:A11) on blank cells to create the tables, all the cells get filled with "Conflicting Entry". Is it possible blank cells show blank all across down

 

[oldbrewer]

so sheet 1 column B has more than 1 * [what is in A7 ] *
when you copy it down it goes to A8 A9 etc