How do I copy data from one workbook to create drop down lists in another workbook

Hi,
This is my first post. I am returning to Vb in Excel after a break of 12 years and things seem to have moved on a pace. I am very rusty and there do not seem to be as many resources available as there were. I have a problem which I hope community members may be able to help with, as I plod along on my refresher path.

Each sheet in this example represents a separate workbook.
(Example was created on my mac at home but saved in compatibility mode as an Excel 1997-2004 Workbook . The macro will need to work in Excel 2007 on a PC running Windows XP sp3)


'Roll Call' is a workbook on which a supervisor can allocate crews to vehicles on a daily basis.
'Rota' is a workbook with a separate sheet for each month of the year


Actual 'Roll Call' is formatted to print out onto a single A4 sheet. It automatically reads and inserts the day/date into the header.
At the moment, each cell in the Crew column contains a drop down box containing the names of the 22 drivers, which reduces in the successive rows each time a driver is allocated to a vehicle [up to 2 drivers per vehicle], so a driver cannot be allocated to more than one vehicle in error .


'Rota' has a series of acceptable entries. 1,C,SC, all indicate a driver is available for that day.
[Blank cells or cells containing L or T or S means the driver is not available and can be ignored].

Supervisors find this useful but must have both open to see who is available on any given day to select them from the drop down.

They have asked, is there a way to automate 'Roll Call' so that the only names in the drop downs are the drivers available on that day.

I have thought this through and believe it means I have to find a way to create a macro in Roll Call which :-

When 'Roll Call' is opened, it looks up 'Rota',[whether open or closed] and finds the sheet[month] and column for the current date.

it then loops to reads down a range of cells in the current date column for available drivers [accepts 1,C,SC as valid]
if an acceptable code appears in one of the cells, it then reads along the row and concatenates cells A,B,C, in that row to form the employee number and name of the driver.
This is then added to a dynamic list which is the data source for the drop downs in Column C of 'Roll Call'.

I have limited recent experience of VB and this is beyond me.
Can anybody help.
Regards,
Mikeuk24
example.xlsx