General Database Problems to make it more user friendly.

Hi Experts,
This is my first post, only recently started using more functions in excel now that I'm learning to make databases.

My example is a student attendance list which i got from about.com (attached).

Columns are Student ID, Surname, Initial, Age, Program, Building, Classroom.

1. I'm mainly using drop down lists to add the data to table, such as assigning a program for each student.
Is it possible to make the drop down list automatically update if a new program is added or a name is changed? Example, if English was to be change to English Literature, and then a new program, English Language, was added. The database would automatically change english students to english lit students, and also make english language available in the drop down without having to redo the data validation function.

2. I've got a row at the bottom with some totals, average age, mode etc..
Is it possible to make these totals, change when data is filtered out.
Example, when no data is filtered the average of all the students ages is shown. However, when i filter the program to a specific subject (biology students say), the average age on the bottom row stays the same, and isn't the average for just a specific program.

Any links or functions that help with this kind of stuff, just mentions them and i can look them up.

This is my first post, if i've made any newbie posting errors just say.

Thanks for your help.

Hi Ace,
Cheers for the answer. That worked for all the data but didn't change as the filter was applied.

I did however use the dsum function and also advantced filtering option. While these worked on my practice sheet, they didn't work on my project.
Something about missing or illegal field names.
Does anyone had this themselves? I've filled in the arguments exactly how i did for my practice but doesn't work on my project.