Timetable with Calc

asked 2019-01-01 18:55:03 +0200

alex72gr
13 ●5 ●7 ●12

updated 2019-01-01 18:55:28 +0200

Hello! I create the timetable for my school using LibreOffice Calc. My school has private-like lessons, i.e. lessons with one student (or sometimes a couple of students) per teacher (and per hour). So, in the spreadsheet I assign for each student the corresponding teacher. Please take a look on the sample file (attachment). The upper part is the timetable that I work on (timetable per student), and the lower part is a "automatic" representation of each teacher's schedule. "X" denotes that the student is not available on this specific hour (time slot). The schedule of the teachers is "read" from the students timetable with a function (combination of if and countif). What I need: when a teacher is impossible to have lesson for a specific hour, I need an "X" to be displayed in this specific hour on the teacher's schedule (lower part of the spreadsheet) AUTOMATICALLY. I mean that I need some kind of function in these cells that could read for each one of the teachers, if there is an available student for the specific hour. If there is no student available, I need the cell on the teachers schedule to have an "X". For example take a look on the teacher "Mary", Monday 2nd hour. It's impossible for her to have a lesson because none of her students (neither STUDENT_02, nor STUDENT_09 are available on this specific hour). So, the corresponding cell on the lower part (Monday, 2nd hour for Mary, at the lower part of the spreadsheet, should have an "X", which should be placed automatically). Is there any way to do it? Thanks a lot!

C:\fakepath\timetable_sample.ods

edit retag flag offensive close merge delete

add a comment

answered 2019-01-01 21:05:47 +0200

Lupp
13204 ●37 ●106 ●193

updated 2019-01-02 14:26:36 +0200

See this reworked example.

In the TEACHER's table the row-addressing of ranges was wrongly relative in parts. I rectified that.

I also suggested a possible solution to what you want (as far as I had an understanding) in the attached document.
That solution relies on the LibO Calc function TEXTJOIN which only is available and well working in LibO versions 5.3.3release and higher. The formulae in the helper column need to be entered for array-evaluation. Simplified the solution to avoid the TEXTJOIN function.

===Edit1 2019-01-01 23:48 CET===
As announced in my recent comment I attach this enhanced demonstration. It uses TEXTJOIN() again and requires LibreOffice vrsion 5.3.3 or higher therefore. (It will not work in AOO.)

===Edit2 2019-01-02 14:27 CET===
This new attachment contains some suggestions concerning the requests from a next comment by the OP.

edit flag offensive delete link

Comments

I checked your file. It works! Thank you! I just need to see carefully (and understand) the combination of functions you used.

alex72gr ( 2019-01-01 22:23:47 +0200 )edit

My simplified solution is nearly the same as that by SM_Riga. =SUMPRODUCT(ArrayReference=ValueOrCellReference) and
COUNTIF(ArrayReference;"=" & ValueOrCellReference) or just
COUNTIF(ArrayReference; ValueOrCellReference) both help to find the number of matches. There is a tiny difference, however, for what I prefer the SUMPRODUCT() (despite the silly name).
The more relevant difference was in my original solution where I also produced a list of students per teacher in a helper column. That might be useful sometimes beyond the "eXing-out". Tell me if you want to see that withdrawn variant again.
By the way: For what reason is teacher JHN listed twice?

Lupp ( 2019-01-01 22:48:03 +0200 )edit

The second "appearance" of JHN was my mistake... :D I would like to check your other variant. I didn't really understand how it works (to be honest, I've not completely understood how this solution works, but anyway I made the necessary modifications on the formula for my spreadsheet, and it seems that it works just perfectly!). By the way, if one needs to "export" the timetable per teacher is there any "function" to do it? Just imagine that you need to provide John with his own timetable. You need a timetable with the name of each student (for student_01, student_06) placed in the correct cell (time slot).The same applies for the other teachers... But... is there any way to do it? Thanks again! (I think that I should post a new question...).

alex72gr ( 2019-01-01 23:33:45 +0200 )edit

I will attach another solution where you find the students listed per teacher in an ectra column - just for information without further evaluation.
In addition I enhanced the formulae to put the respective student into the teachers' timetable. I also allowed for more than one student in a single lesson and demonstrated the case. Concerning the printing:
If your example is realistic I see no need for specialised prints per teacher.
Otherwise a specific suggestion should be based on the details.

Lupp ( 2019-01-01 23:46:31 +0200 )edit

Thank you again! I'm looking forward to checking your new formulas and the functionality of the spreadsheet.

alex72gr ( 2019-01-01 23:52:43 +0200 )edit

Oh! I got your file! GREAT!!! I'm going to check again your formula tomorrow. To be honest, I can't understand how it works. I'm sure that you have a really profound knowledge on spreadsheets. Thank you again!

alex72gr ( 2019-01-02 01:07:04 +0200 )edit

Hello again! Lupp, I checked your formula again. Unfortunately, what I can understand is just that it works. I need to read in detail the manual for each one of the functions you used. This form of the timetable (per teacher, each cell has the "names" of the students) is good for printing. It would be better if the "X" and "-" were missing. Could you send me a variant of the formula which DOES NOT display the "X" and "-" in the teachers timetable?I don't need this symbols for printing. Thank you!

alex72gr ( 2019-01-02 10:11:12 +0200 )edit

Wow! I just checked the new file. More than GREAT, and much more than I asked... One trillion thanks!!!

alex72gr ( 2019-01-02 15:18:52 +0200 )edit

Lupp, a question about the enhanced demonstration file (Edit1 2019-01-01 23:48 CET)... The cell F41 has the following formula:

=TEXTJOIN(CHAR(10),1,IF(F$7:F$17=$D41,$E$7:$E$17,"")) & IF(CURRENT()="",IF(SUMPRODUCT($D$7:$D$17=$D41,F$7:F$17<>"X")=0,"X","-"),"")

This formula results in the cell F41 to display either the student(s), or "X" or "-". I need to F41 to display only the student(s) (if there are no students for this time slot and teacher, the cell should remain empty) and never "X" or "-". This is enough for printing the timetable for the teachers and actually it's more "clear" for them (most of them are going to ask what does "X" and "-" mean). I tried to make some modifications on the formula, but I failed, since I haven't completely understood the syntax (and functionality) of ...(more)

alex72gr ( 2019-01-02 20:20:03 +0200 )edit

(The sheets I saw don't use a cell F41.)
You originally wanted the "X" or "-" respectively to support the planning, I suppose. Therefore I didn't remove that from the 'Planning' sheet, but created the sheets 'PrintingMany' and 'PrintingFewSelected' in the next demo attached under "Edit2". There the "supporters" are no longer included.
Generally I would advise not to also use sheets for data keeping or planning for printing. Printing may require formatting and scaling in a way that can be counterproductive otherwise.
Anyway you find the formula you seem to look for in PrintingFewSelected.F4 of the final example document.

Lupp ( 2019-01-02 20:43:28 +0200 )edit

see more comments

Timetable with Calc

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

Timetable with Calc edit

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

Timetable with Calc