I am trying to setup a template that summarizes data from other tabs within
the same spreadsheet. I have a proprietary application that spits out an
excel worksheet that has predefined tab names (eg. males, females). I want
to insert a summary worksheet that has calculations based on the various
tabs.

When I first tried to do this I set up a template, then copied the template
sheet into my datasheet. Problem is the filename reference appeared in the
formulas, referring to the template sheet, not the data sheet I was working
with.

Next I setup a formula to calculate the filename (this worked great) loacted
in cell A2. My intention was to include the file name in the formulas using
A2, but this didnt' work. Here is what I was trying to do:

='['&A2&']Males'!A2
where A2 contained the file name DataFileDec2007.XLS

Any ideas how I can do this?