Eliminate need for duplicating source worksheets for hundreds of workbooks

I have 125 Excel 2010 workbooks. Each has two worksheets labeled Form and Data. Each of the Form worksheets is unique, using formulas to pull some data from the Data worksheet. The Data worksheet is identical for all 125 workbooks.

Here is the problem: Occasionally information on the Data worksheet changes and I find myself opening all 125 workbooks to update the Data worksheet.

Complications: The workbooks are stored on a server and opened by many different employees, some who might have a problem spelling Excel without the aid of a dictionary. This is the reason the Data worksheet was initially integrated into each workbook.

I would like to come up with a solution in which I have just one Data worksheet and all of the Form worksheets somehow populate fields from that one Data worksheet. One idea I had was to launch a macro when a workbook is opened. The macro would open the Data worksheet, allowing the Form worksheet to properly populate the various cells. I have been seeing #REF in my sleep and am clueless about how to resolve.

Any suggestions would be most appreciated.

Thanks for the suggestion, MarvinP. I'm afraid it is I who now has the spelling problem...how do you spell PowerPivot? I downloaded PowerPivot, looked around, watched several videos and concluded that this thing is waaaay above my head at the moment. I need more help...please!

The PivotTable add-in seems to be dealing with tables of external data. My situation may be different so I should explain further about my Data worksheet. It is NOT a spreadsheet of a table of data, per se. It consists of snippets of data in different places. Think of it as a worksheet of dozens of tiny tables separated by empty columns or rows, some accessed by many Form worksheets, others unique to a single Form worksheet. For example:

- a list of employees accessed from the Form worksheet via VLOOKUP located at A1
- a list of project addresses accessed from the Form worksheet via VLOOKUP located at G1
- multiple lists of Form-specific text entries that are selected via the CHOOSE function located at S1, A32, J40, W95
- lists of numbers and values, again specific to a particular Form worksheet, providing answers for VLOOKUP or CHOOSE functions, again located in other non-contiguous areas of the Data worksheet.

If the above disclosure does not rule out using PowerPivot, please consider the following questions?

First, can you give me a couple pointers on how to access that one Data file from the many Form worksheets? I have no idea where to start. Do I modify every workbook somehow? Does PowerPivot need to be loaded on each user's system?

Second, is this something I can set up in such a way as to not impact my Excel newbie users?

Thanks in advance for your guidance.

MORE INFO AND CLARIFICATION:

Excel formulas cannot reference files that are not open. This is the reason that I have been forced to replicate the Data worksheet for each of the 125 Excel worksheets. If the 125 Excel Form worksheets were able to refer to a closed file i think my problem would be solved.

Any other suggestions?

Thanks, MarvinP for your suggestion. It worked. Kinda.

I was able to pull data in from the closed workbook and display it as desired. But the problem mentioned in my initial post still exists:

All formulas on the Form worksheet show #REF. Maybe I am missing something, but i cannot seem to get these formulas to display the VLOOKUP or CHOOSE values from the Data worksheet that I have now successfully copied from the closed workbook.

So, while I can now pull in the Data from just one worksheet, the formulas on the Form worksheet do not work.

Any thoughts?