Rearrange data for a school timetable

Discuss your migration story or project

Rearrange data for a school timetable

Postby jadawl01 » Mon Aug 24, 2009 3:43 pm

Well
I am a total newbie, but I am wiling to learn
I am trying to format school timetables before printing them
I have some raw data in a .ods file produced by a timetabling program
like this:

fetrowtimetable.png

I need data to be formated like this layout:
fettimetable.png
fettimetable.png (6.52 KiB) Viewed 7846 times

Basically, I need to
1- pull the table's head (in red) from teacher column E

2- pull subject, student set, and room related to a given teacher and put them together into a single cell, according to day and period
fo example, data from row 2, (with A2=friday1 and B2=09:00) from row spreadsheet
would be put at C7 int the formatted timetable

3- in order to prepare data , I also need to check if the a cell in day column contain the number 2
if so transform period by adding 6
for example, for day tuesday2
tranform day tuesday2 period 08:00 into tuesday1 period 14:00
tranform day tuesday2 period 09:00 into into tuesday1 period 15:00
tranform day tuesday2 period 10:00 into into into tuesday1 period 16:00
and so on for other XXX2 days

here is the .ods file
http://www.mediafire.com/download.php?t2jiagzlm0o
Hope I explained my problem clearely
OpenOffice 2.3.1
on Window XP
jadawl01
 
Posts: 8
Joined: Mon Aug 24, 2009 3:40 pm

Re: Rearange data for a school timetable

Postby squenson » Mon Aug 24, 2009 6:49 pm

I attach an example for you to study. If you have further questions, let me/us know!

In the table sheet, I have added a few columns to prepare the data. It should not be too difficult to understand. I put comments on the yellow cells.

The TimeTable sheet contains the timetable for ONE teacher at a time, that you can select by clicking on the top cell of that sheet, then a combo box appears.

The key formula is in the timetable: it takes the teacher, the day and the period and search the corresponding value in the table sheet. If not found (the purpose of the ISNA(...) function) I return nothing, if found, I return the content of the cell that I have prepared in column K (11th column of the table).
Attachments
farabitimetable.ods
(51.05 KiB) Downloaded 549 times
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: Rearange data for a school timetable

Postby jadawl01 » Mon Aug 24, 2009 7:34 pm

Thank so much for help
I will try to use your instructions
I am really willing to learn
OpenOffice 2.3.1
on Window XP
jadawl01
 
Posts: 8
Joined: Mon Aug 24, 2009 3:40 pm

Re: Rearange data for a school timetable

Postby jadawl01 » Mon Aug 24, 2009 10:26 pm

it is working great for me :)
but, could you please tell me how you did it? is there any Macro ? where can I find it?
because I need also to make timetables Right To Left (arabic) like this:

17:00 16:00 15:00 14:00 11:00 10:00 09:00 08:00
------|-------|------|------|------|------|------|-------|--------
-------------------------------------------------------------- Monday
--------------------------------------------------------------Tuesday
OpenOffice 2.3.1
on Window XP
jadawl01
 
Posts: 8
Joined: Mon Aug 24, 2009 3:40 pm

Re: Rearange data for a school timetable

Postby squenson » Mon Aug 24, 2009 10:42 pm

No macros, only formulas using the left column and top row of the table. Doing it in right to left should not be a problem, if you respect the proper references of cells.
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: Rearange data for a school timetable

Postby jadawl01 » Tue Aug 25, 2009 1:17 am

It works like a charm, but Could you please elaborate more :oops:
for example, some of those funtions with comments...
or point me to some tutorials on internet to read about those functions
I am taking the problem I am facing as a starting point to learn more
OpenOffice 2.3.1
on Window XP
jadawl01
 
Posts: 8
Joined: Mon Aug 24, 2009 3:40 pm

Re: Rearange data for a school timetable

Postby squenson » Tue Aug 25, 2009 6:02 am

Please be more specific for the parts of formulas you do not understand. If you select a cell with a formula, you can press on the toolbar the symbol fx which gives more information about how its elements are constructed, You also have a Help button that gives a detailed description of each function and its parameters.
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: Rearange data for a school timetable

Postby jadawl01 » Tue Aug 25, 2009 2:21 pm

Thank so much
Now, I am reading some tutorials on how to construct fuctions...
Hope I can make it Right To Left myself
if I fail, I will come back to you
OpenOffice 2.3.1
on Window XP
jadawl01
 
Posts: 8
Joined: Mon Aug 24, 2009 3:40 pm


Return to Institutions & Educational

Who is online

Users browsing this forum: No registered users and 2 guests