With xls name in A1.
=INDIRECT("'["&A1&".xls]Sheet2'!G4:G12")

With xls name in A1 and cell reference in A3
=INDIRECT("'["&A1&".xls]Sheet2'!"&A3)

With name in A1 and sheet name in A2 and cell reference in A3
=INDIRECT("'["&A1&".xls]"&A2&"'!"&A3)

If this post helps click Yes
---------------
Jacob Skaria


"SimoninParis" wrote:

Hi all,

Here's a question the answer to which I am sure I used to know when I used
Excel practically everyday. However, years have gone by and what hair hasn't
fallen out has gone grey.....

I have about a hundred Excel files of the same format - each has two sheets.
They contain information (of course) some of which I want to collate onto one
separate worksheet on in a new Excel file.

Each Excel file refers to a person and is named in the format "John
Smith.xls". If I put all the names in column A of the new worksheet, can I
construct a formula in column B along the lines of "=somme([the name in
column A.xls] $sheet 2 $g$4:$g$12)" ? And yes, I'm using a French language
version of Excel.

I hope the question makes sense and I await the advice of someone more
gifted in Excel matters than me.

Many thanks, in advance,
Simon