Drop down values to auto update when sources are updated

[Emma Ccycling]

Hello

I've been trying to figure out a solution to this for a few days but i'm completely stuck!

I have a table of data which contains the sources (6370) for 6370 separate dropdowns. Each source corresponds to a tutor and date. There are 14 tutors and 455 dates hence the 6370 separate dropdowns.

Each source contains 4 values with the top value updating depending on values in another sheet (if that tutor is booked onto a course on that date it will change to 'Booked')

[TABLE="width: 500"]
<tbody>[TR]
[TD]Danielle Riley/ Booked[/TD]
[/TR]
[TR]
[TD]Busy[/TD]
[/TR]
[TR]
[TD]Tentative[/TD]
[/TR]
[TR]
[TD]Bank Holiday[/TD]
[/TR]
</tbody>[/TABLE]

There is then a table by date and tutor with dropdowns for the tutors availability on each day.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Tutor[/TD]
[TD]01-Jan-18[/TD]
[TD]02-Jan-18[/TD]
[/TR]
[TR]
[TD]Danielle Riley[/TD]
[TD]Danielle Riley[/TD]
[TD]Booked[/TD]
[/TR]
</tbody>[/TABLE]

If the top value in the source is updated (ie a tutor is booked onto a course or taken off a course) then i want this to auto update the value shown in the dropdown.


For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Tutor[/TD]
[TD]01-Jan-18[/TD]
[TD]02-Jan-18[/TD]
[/TR]
[TR]
[TD]Danielle Riley[/TD]
[TD]Danielle Riley[/TD]
[TD]Booked[/TD]
[/TR]
</tbody>[/TABLE]

If i book Danielle Riley onto a course on 01-Jan-18 then the source value will update to 'Booked' I then want the value in the dropdown to auto update to show that she is booked on that date.

Any help would be greatly appreciated!

Thanks