Filename in formula

[Kim]

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?

 

[rdwj]

Kim,

If I understand this correctly you ended up with formula's linked to your
template workbook, where they should point to the data workbook?
Have you tried to edit the link location through Edit, Links, Change Source?

Otherwise you might want to look at the "Indirect" functionality

rdwj

 

[T. Valko]

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 here:

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.