I have a lesson plan spreadsheet where I have a list of tasks for students to complete in lesson plan workbooks (one lesson plan workbook gets created for each week). The list of available tasks to put in the lesson plan is referenced using Data Validation from a Named Range in another workbook.

I am stuck on getting excel to do two things...

1. When a student completes a task in a given week's lesson plan, I want to automatically update the appropriate row in another workbook showing they completed that task.

2. Then when I create another lesson plan workbook, I want only the un-completed tasks for a given student to show up in the Data Validation list (see 1 above as the reference workbook that gets updated with completed tasks by student)

Any suggestions?

Am I expecting too much here from Excel?

Also FYI -- this spreadsheet uses Data Validation to build a concatenated Named Range that then references the appropriate task list. Student tasks are grouped into Activity Groups, Age Range, and Category. So in order to filter down to only a small group of tasks, I'm first selecting Activity (example: Music) then Age Range (example: 4to6 years), then Category (example: FineMotor) and using these selections to dynamically build a Named Range. This Named Range (i.e. =INDIRECT(SUBSTITUTE(A1&A2," ","")) ) goes into the Data Validation box. That part all works just fine -- now I need to filter out the already completed tasks from each available named range task list.

Thanks for any help in advance!