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

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

You can do this if the file is open at the same time by using
INDIRECT:

=SUM(INDIRECT("'["&name_cell&".xls]Sheet2'!G4:G12"))

where name_cell is the cell containing the name, eg A2. With other
names in A3, A4 etc you can copy this down as required (as long as the
files are open).

However, INDIRECT will only work with open files, and it doesn't seem
practical in this case as you have a hundred files.

So, you might like to download a free add-in, morefunc, which has a
function INDIRECT.EXT which will work on closed workbooks. Do a Google
search to find sites where you can get it - there is a French language
version. You will need to include the full path in front of the
[ above.

Hope this helps.

Pete

On Sep 25, 2:43*pm, 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

Jakob Skaria's response appeared to be the solution - I had to play around
with the formula (adding the sum() bit) and after it didn't work the first
time, I opened one of the files concerned and found it gave me the answer I
was expecting.

Pete_UK's idea of an add-in, if it works, is just exactly what I want!

Thanks to both of you,

Simon

"Pete_UK" wrote:

You can do this if the file is open at the same time by using
INDIRECT:

=SUM(INDIRECT("'["&name_cell&".xls]Sheet2'!G4:G12"))

where name_cell is the cell containing the name, eg A2. With other
names in A3, A4 etc you can copy this down as required (as long as the
files are open).

However, INDIRECT will only work with open files, and it doesn't seem
practical in this case as you have a hundred files.

So, you might like to download a free add-in, morefunc, which has a
function INDIRECT.EXT which will work on closed workbooks. Do a Google
search to find sites where you can get it - there is a French language
version. You will need to include the full path in front of the
[ above.

Hope this helps.

Pete

On Sep 25, 2:43 pm, 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

You're welcome, Simon - thanks for feeding back.

Pete

On Sep 25, 3:44*pm, SimoninParis
wrote:
Jakob Skaria's response appeared to be the solution - I had to play around
with the formula (adding the sum() bit) and after it didn't work the first
time, I opened one of the files concerned and found it gave me the answer I
was expecting.

Pete_UK's idea of an add-in, if it works, is just exactly what I want!

Thanks to both of you,

Simon *



"Pete_UK" wrote:
You can do this if the file is open at the same time by using
INDIRECT:

=SUM(INDIRECT("'["&name_cell&".xls]Sheet2'!G4:G12"))

where name_cell is the cell containing the name, eg A2. With other
names in A3, A4 etc you can copy this down as required (as long as the
files are open).

However, INDIRECT will only work with open files, and it doesn't seem
practical in this case as you have a hundred files.

So, you might like to download a free add-in, morefunc, which has a
function INDIRECT.EXT which will work on closed workbooks. Do a Google
search to find sites where you can get it - there is a French language
version. You will need to include the full path in front of the
[ above.

Hope this helps.

Pete

On Sep 25, 2:43 pm, 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- Hide quoted text -

- Show quoted text -