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"
[TABLE="width: 1257"]
<tbody>[TR]
[TD]4 10th
(East)

[/TD]
[TD] Maths
Sir William
10th - East
[/TD]
[TD] Bio
Mis Emily
10th - East
[/TD]
[TD][/TD]
[TD]Chem MIs Lily
10th - East
[/TD]
[TD] French
Sir William
10th-East
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1257"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1257"]
<tbody>[TR]
[TD]5 10th
(West)

[/TD]
[TD]Maths
Sir William
10th - West
[/TD]
[TD]Bio
Mis James
10th - West
[/TD]
[TD]Chem
Mis Lily
10th-West
[/TD]
[TD]Science
MIs Lily
10th - West

[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]




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"

[TABLE="width: 1257"]
<tbody>[TR]
[TD]4 Sir William


[/TD]
[TD] Maths
Sir William
10th - East

Maths
Sir William
10th - West
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD] French
Sir William
10th-East
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1257"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1257"]
<tbody>[TR]
[TD]5 Mis Lily

[/TD]
[TD][/TD]
[TD] Chem
Mis Lily
10th-East

Biology
Mis Lily
10th-West
[/TD]
[TD] Science
MIs Lily
9th - West
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






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]

[TABLE="width: 832"]
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]CLASS TIMETABLE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]lesson1[/TD]
[TD]lesson2[/TD]
[TD]lesson3[/TD]
[TD]lesson4[/TD]
[TD]lesson5[/TD]
[TD]lesson6[/TD]
[TD]lesson7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]class1[/TD]
[TD]english[/TD]
[TD]maths[/TD]
[TD]history[/TD]
[TD]french[/TD]
[TD]geography[/TD]
[TD]sport[/TD]
[TD]science[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]class2[/TD]
[TD]history[/TD]
[TD]french[/TD]
[TD]geography[/TD]
[TD]sport[/TD]
[TD]science[/TD]
[TD]english[/TD]
[TD]maths[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]class3[/TD]
[TD]sport[/TD]
[TD]science[/TD]
[TD]english[/TD]
[TD]maths[/TD]
[TD]history[/TD]
[TD]french[/TD]
[TD="colspan: 2"]geography[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]TEACHER ASSIGNMENT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]lesson1[/TD]
[TD]lesson2[/TD]
[TD]lesson3[/TD]
[TD]lesson4[/TD]
[TD]lesson5[/TD]
[TD]lesson6[/TD]
[TD]lesson7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]class1[/TD]
[TD="class: xl22"]T1[/TD]
[TD="class: xl22"]T2[/TD]
[TD="class: xl22"]T3[/TD]
[TD="class: xl22"]T1[/TD]
[TD="class: xl22"]T4[/TD]
[TD="class: xl22"]T5[/TD]
[TD="class: xl22"]T6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]class2[/TD]
[TD="class: xl22"]T7[/TD]
[TD="class: xl22"]T8[/TD]
[TD="class: xl22"]T4[/TD]
[TD="class: xl22"]T3[/TD]
[TD="class: xl22"]T1[/TD]
[TD="class: xl22"]T8[/TD]
[TD="class: xl22"]T2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]class3[/TD]
[TD="class: xl22"]T8[/TD]
[TD="class: xl22"]T4[/TD]
[TD="class: xl22"]T1[/TD]
[TD="class: xl22"]T2[/TD]
[TD="class: xl22"]T1[/TD]
[TD="class: xl22"]T4[/TD]
[TD="class: xl22"]T5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]CONFLICT ANALYSIS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]lesson1[/TD]
[TD]lesson2[/TD]
[TD]lesson3[/TD]
[TD]lesson4[/TD]
[TD]lesson5[/TD]
[TD]lesson6[/TD]
[TD]lesson7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T1[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]2[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T2[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T3[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T4[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T5[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T6[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T7[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]T8[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]0[/TD]
[TD="class: xl22"]1[/TD]
[TD="class: xl22"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]the cell containing the 2 (teacher 1 lesson 6)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]can be highlighted in red to show conflict[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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