Linking Between Workbooks

Hello, recently I was asked to put together a kind of tracking system. Essentially this system would track the completion of survey action items. To make a long story short, I used Excel to do this, and it works, but it is incredibly complicated. This is only a temporary assignment for me and I will not be here next year to update the spreadsheet and I don't think my manager wants to go through and try to do it. I'm wondering if there is a better, or easier way to do this, or a better program.

Basically, I have separate workbooks for each manager, which they have access to. They can go in to this workbook and select each individual action item and from a pull-down menu select whether the task it Open, In-Progress, or Complete. Based on what the drop-down menu displays, the main workbook (which contains all of the managers) will then update to show how many items they have that are open, in-progress, or complete. The problem is that the formula seems really long and complicated. For example, it reads:

=IF[DunnNeedhamActionPlan.xls]Sheet1'!$F$6="Open","1",IF('C:\Users\E9769751\Desktop\[DunnNeedhamActionPlan.xls]Sheet1'!$F$6="In-Progress","0",IF('C:\Users\E9769751\Desktop\[DunnNeedhamActionPlan.xls]Sheet1'!$F$6="Completed","0")))+IF('C:\Users\E9769751\Desktop\[DunnNeedhamActionPlan.xls]Sheet1'!$F9="Open","1")+IF('C:\Users\E9769751\Desktop\[DunnNeedhamActionPlan.xls]Sheet1'!$F$12="Open","1")+IF('C:\Users\E9769751\Desktop\[DunnNeedhamActionPlan.xls]Sheet1'!$F$15="Open","1")+IF('C:\Users\E9769751\Desktop\[DunnNeedhamActionPlan.xls]Sheet1'!$F$18="Open","1")

Essentially it tells the cell to look into the other workbook and depending on the status of the cell, update the count for Open, In-Progress, and Completed.
As I said earlier, this will probably be too complicated or too time-consuming for my managers to do next year so I'm wondering there is an easier way, especialy if I need to add a new workbook to the bottom of the main list.

Any help or suggestions on Excel forumulas or even other programs that might handle this better would be appreciated. Thanks.