Filenames and formulae

  • Thread starter Thread starter SimoninParis
  • Start date Start date
S

SimoninParis

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

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
Click to expand...
Click to expand...
 
You're welcome, Simon - thanks for feeding back.

Pete

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 answerI
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 said:
You can do this if the file is open at the same time by using
INDIRECT:
=SUM(INDIRECT("'["&name_cell&".xls]Sheet2'!G4:G12"))
Click to expand...

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).
Click to expand...
However, INDIRECT will only work with open files, and it doesn't seem
practical in this case as you have a hundred files.
Click to expand...
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.
Click to expand...
Hope this helps.
Pete
Click to expand...
Hi all,
Here's a question the answer to which I am sure I used to know when Iused
Excel practically everyday. However, years have gone by and what hairhasn'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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top