Inter-relating Combo Boxes in Userforms based on data in tables

Hi Everyone,

I was wondering if any of you lovely lovely people could help me at all?

I have worked out (through reading and what not) how to link a combo box to a range in a worksheet.

What I need to do is relate what is selected in that combo box, to another combo box which picks from another range.

I'll explain.

Basically I have a data spreadsheet called "Offices"

It's set up as follows

(Sorry, it's really frustrating trying to format it to a table type thing but you get the gist)

ROW NUM______COLUMN A____________COLUMN B
---------------------------------------------------------------
[email protected]
---------------------------------------------------------------
2_________________________________OFFICE 1
3_________________________________OFFICE 2
4_________________________________OFFICE 3
5_________________________________OFFICE 4
---------------------------------------------------------------
[email protected]
---------------------------------------------------------------
7_________________________________OFFICE 2
8_________________________________OFFICE 3
9_________________________________OFFICE 4


The dashed lines just show borders around cells so you can split them up.
The solid lines i've had to put in to space it out...
Column B rows 2 to 5 are named as range "DistrictOffice1_List" and Column B rows 7 to 10 are named as range "DistrictOffice2_List"

Now, in my userform, lets say its called "InputScreen".
I have a combo box called "DistrictOffice" and below that I have "Office"

I want the DistrictOffice combo to show all the district offices to pick from column A excluding any cells that aren't filled in i.e. rows 2-5 and 7-9.

Then I want the Offices combo box to automatically populate with all the offices for that DistrictOffice sourced from column B i.e. based on DistrictOffice selected so that if "DistrictOffice1" were selected in the "DistrictOffice" combo, the "Offices" combo only shows "OFFICE5, OFFICE6, OFFICE7".

If anyone can help that would be amazingly helpful!!! Thanks in advance :-)