Populating a calendar?

[Discussions63]

I have slowly been teaching myself VBA while building a training database (with the help I get from members on here) . On one sheet I have a calendar that I want to have the title show in the cell that reflects the date of training. I would like the data for this to come from a range of other sheets (each sheet displays the information about each training session, which can increase in the number of sheets, depending on the amount of training sessions)

In the information sheet I have a cell that selects a date through a date picker and a cell that has the name of the session. I would like to have it that when I select a date the name of the session populates the calendar sheet on the relevant date.

Cheers
Rob

 

[oldbrewer]

col I

01/01/2016

02/01/2016

03/01/2016

04/01/2016

row 8

#######

welding

cutting

grinding

this table automatically pulls data

select date

from the details sheet

02/01/2016

note there is no course on 4/1/16

@@@@

cutting

so a blank is returned

#####

=IF(ISERROR(OFFSET($O$20,MATCH(I8,$O$21:$O$23,0),1)),"",OFFSET($O$20,MATCH(I8,$O$21:$O$23,0),1))

details sheet

@@@@@

row 21

02/01/2016

cutting

4 hours

03/01/2016

grinding

6 hours

=OFFSET($H$8,1,MATCH(D15,I8:K8,0))

01/01/2016

welding

7 hours

col O

<colgroup><col span="3"><col><col span="4"><col span="4"><col span="2"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Discussions63]

Sorry but this formatting doesn't help. Not sure if I know enough yet to decipher this. You may need to dumb it down a little.
Cheers
Rob

 

[oldbrewer]

you have a separate sheet for each training session. I suggest you have just one sheet with details of the training sessions on separate rows - it makes it much easier to handle. I will get back to you in a few hours with a more simplified suggestion

 

[Discussions63]

you have a separate sheet for each training session. I suggest you have just one sheet with details of the training sessions on separate rows - it makes it much easier to handle. I will get back to you in a few hours with a more simplified suggestion

Unfortunately I cannot have them all on one sheet, we will have over 30 different types of training and to place them all on one sheet would cause problems with navigation.

 

[Discussions63]

Unfortunately I cannot have them all on one sheet, we will have over 30 different types of training and to place them all on one sheet would cause problems with navigation.

The only way I think I could make that happen would be to create a sheet that had all the data and a form that retrieved and inputted the data for each course but this is well outside of My excel knowledge.

 

[oldbrewer]

I was thinking one row for each training type, each cell in the row would show all details of the course. Easy to put the dates onto your calendar. And a separate sheet would provide a nicely formatted version of a particular training course, automated so that when you entered course name it all pops up. Let me know if you want an example of how it would work.

 

[Discussions63]

I have had a look at the one sheet option and it just doesn't work for what I am trying to achieve. Each training type information has to be presented as an information sheet for staff to review. I have an information sheet for each training type and then when addition training courses are included then a new copy of the blank information sheet is created and completed. The idea is that when a date range for the training is selected that it will populate the Calendar which is on another sheet with the title of the training. I am also then going to set up a hyperlink from the calendar for each specific training course back to the relevant information sheet. The intention is that I will be the administrator of this document but many other staff will have read only access. The information sheets are important so that supervisors can gain an understanding of the training and decide if their staff should be involved in that particular course. I can send you a sample of spreadsheet as it is so far if that would assist.
Cheers, Robin