Vba code to update range on multiple worksheets

Hi guys,
I've been reading this forum for a while now, picking up some great tips along the way too but this is my first post on here.
I'm very much a novice when it comes to vba so any help with the following problem will be greatly appreciated. I have a 2 part question:

I have a spreadsheet (simplified example attached) consisting of 2 w/s where I keep a record of employee non-attendance days.
Sheet 1 ('Data') has a table of consisting of 5 colums with the following headers (Depts, Name, Surname, Hols, Sick, Req'd)
Each column has a named range.
On Sheet 2 ('Update'), I have a dropdown list that will select Dept names

Part 1
When I select a Dept Number from the dropdown list, I want the table to populate with the corresponding data from the table on Sheet 1 ('Data')
I have it working using formulas but is there a way this can be achieved using vba code (macro)? - This is purely for educational purposes.
I've used data validation to populate the dropdown but can change this if this can be done using vba code

Part 2
When I update the highlighted information, is there a way that the corresponding figures on 'Data' (highlighted) can also be updated depending on which Dept is selected (again using vba/macro)?

The actual worksheet consists of an average of 35 names per Dept and covers 8 Depts so there is a lot of data to enter.
To date I have been using the advanced filter but I want to acheive the above if its possible

Thanks in advance,

regards,
H

I've added some named ranges to each sheet to make this simple, then a standard worksheet_change macro in the Update Sheet module does the rest.

Please Login or Register  to view this content.