You could do this using the INDIRECT function. However, this requires that
the source file *MUST* be open. This is usually not desireable. One
possibility is to install an add-in that has a function that will (probably)
do what you want.

The add-in available he

http://xcell05.free.fr/english/


Look for Morefunc.xll


This has a function called INDIRECT.EXT that will (probably) do what you
want. See the
add-in help on its use.


--
Biff
Microsoft Excel MVP


"Kim" wrote in message
...
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?