Using cell contents in a formula to refer to another file

[SimoninParis]

On Friday, I asked a question and was given INDIRECT and/or INDIRECT.EXT as
possible solutions.

I finally managed to make INDIRECT work but I cannot make INDIRECT.EXT work.

My problem: 120 files with identical architecture - two worksheets each. I
would like to build a new file to give me certain data to be found in each of
the 120 files.

Each file is named after a person. In the new file column A will list each
person. I would like to put in column B:
=SOMME(INDIRECT("'["&A5&".xls]feuil2'!$g$8:$g$27"))
Please note that although I am English, my Excel is French.
This works if the named file is open. I would like to have a formula which
returns the correct information for closed files. If I substitute
INDIRECT.EXT for INDIRECT, it does not work. What is the required syntax?

If I use the "Formules" tab and click on IDIRECT.EXT in the "fonctions"
drop-down menu, it asks me for "Adresse, Volatile, Style, Mode"; I don't know
what to put where.

Any/all help will be greatly appreciated.

 

[Bernard Liengme]

That function (INDIRECT.EXT) is found in Laurent Longre's addin
(morefunc.xll) at:
http://xcell05.free.fr/
Download and install it
best wishes

 

[Pete_UK]

Simon,

you will need to include the full path to your file, something like
this:

=SOMME(INDIRECT.EXE("'C:/temp/["&A5&".xls]feuil2'!$g$8:$g$27"))

where I am assuming your files are in the folder C:/temp - change as
necessary.

Hope this helps.

Pete

 

[Pete_UK]

Sorry, should have been back-slashes:

=SOMME(INDIRECT.EXE("'C:\temp\["&A5&".xls]feuil2'!$g$8:$g$27"))

Hope this helps.

Pete

Simon,

you will need to include the full path to your file, something like
this:

=SOMME(INDIRECT.EXE("'C:/temp/["&A5&".xls]feuil2'!$g$8:$g$27"))

where I am assuming your files are in the folder C:/temp - change as
necessary.

Hope this helps.

Pete

On Friday, I asked a question and was given INDIRECT and/or INDIRECT.EXT as
possible solutions.

I finally managed to make INDIRECT work but I cannot make INDIRECT.EXT work.

My problem:  120 files with identical architecture - two worksheets each. I
would like to build a new file to give me certain data to be found in each of
the 120 files.

Each file is named after a person. In the new file column A will list each
person. I would like to put in column B:  
=SOMME(INDIRECT("'["&A5&".xls]feuil2'!$g$8:$g$27"))
Please note that although I am English, my Excel is French.
This works if the named file is open. I would like to have a formula which
returns the correct information for closed files. If I substitute
INDIRECT.EXT  for INDIRECT, it does not work. What is the required syntax?

If I use the "Formules" tab and click on IDIRECT.EXT in the "fonctions"
drop-down menu, it asks me for "Adresse, Volatile, Style, Mode"; I don't know
what to put where.

Any/all help will be greatly appreciated.- Hide quoted text -

- Show quoted text -

 

[SimoninParis]

Yes, I know. I have downloaded it. I just can't make it work for me.

That function (INDIRECT.EXT) is found in Laurent Longre's addin
(morefunc.xll) at:
http://xcell05.free.fr/
Download and install it
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

On Friday, I asked a question and was given INDIRECT and/or INDIRECT.EXT
as
possible solutions.

I finally managed to make INDIRECT work but I cannot make INDIRECT.EXT
work.

My problem: 120 files with identical architecture - two worksheets each.
I
would like to build a new file to give me certain data to be found in each
of
the 120 files.

Each file is named after a person. In the new file column A will list each
person. I would like to put in column B:
=SOMME(INDIRECT("'["&A5&".xls]feuil2'!$g$8:$g$27"))
Please note that although I am English, my Excel is French.
This works if the named file is open. I would like to have a formula which
returns the correct information for closed files. If I substitute
INDIRECT.EXT for INDIRECT, it does not work. What is the required syntax?

If I use the "Formules" tab and click on IDIRECT.EXT in the "fonctions"
drop-down menu, it asks me for "Adresse, Volatile, Style, Mode"; I don't
know
what to put where.

Any/all help will be greatly appreciated.

 

[SimoninParis]

Pete,

Thanks for your help. I've got it to give me the right answer BUT
INDIRECT.EXT still only works if the other file is open. What am I doing
wrong?

Simon,

you will need to include the full path to your file, something like
this:

=SOMME(INDIRECT.EXE("'C:/temp/["&A5&".xls]feuil2'!$g$8:$g$27"))

where I am assuming your files are in the folder C:/temp - change as
necessary.

Hope this helps.

Pete

On Friday, I asked a question and was given INDIRECT and/or INDIRECT.EXT as
possible solutions.

I finally managed to make INDIRECT work but I cannot make INDIRECT.EXT work.

My problem: 120 files with identical architecture - two worksheets each. I
would like to build a new file to give me certain data to be found in each of
the 120 files.

Each file is named after a person. In the new file column A will list each
person. I would like to put in column B:
=SOMME(INDIRECT("'["&A5&".xls]feuil2'!$g$8:$g$27"))
Please note that although I am English, my Excel is French.
This works if the named file is open. I would like to have a formula which
returns the correct information for closed files. If I substitute
INDIRECT.EXT for INDIRECT, it does not work. What is the required syntax?

If I use the "Formules" tab and click on IDIRECT.EXT in the "fonctions"
drop-down menu, it asks me for "Adresse, Volatile, Style, Mode"; I don't know
what to put where.

Any/all help will be greatly appreciated.

 

[Dave Peterson]

You haven't shared what's in each of those cells that the formula uses.

You haven't shared your current version of the formula (the one including the
path).

Pete,

Thanks for your help. I've got it to give me the right answer BUT
INDIRECT.EXT still only works if the other file is open. What am I doing
wrong?

Simon,

you will need to include the full path to your file, something like
this:

=SOMME(INDIRECT.EXE("'C:/temp/["&A5&".xls]feuil2'!$g$8:$g$27"))

where I am assuming your files are in the folder C:/temp - change as
necessary.

Hope this helps.

Pete

On Friday, I asked a question and was given INDIRECT and/or INDIRECT.EXT as
possible solutions.

I finally managed to make INDIRECT work but I cannot make INDIRECT.EXT work.

My problem: 120 files with identical architecture - two worksheets each. I
would like to build a new file to give me certain data to be found in each of
the 120 files.

Each file is named after a person. In the new file column A will list each
person. I would like to put in column B:
=SOMME(INDIRECT("'["&A5&".xls]feuil2'!$g$8:$g$27"))
Please note that although I am English, my Excel is French.
This works if the named file is open. I would like to have a formula which
returns the correct information for closed files. If I substitute
INDIRECT.EXT for INDIRECT, it does not work. What is the required syntax?

If I use the "Formules" tab and click on IDIRECT.EXT in the "fonctions"
drop-down menu, it asks me for "Adresse, Volatile, Style, Mode"; I don't know
what to put where.

Any/all help will be greatly appreciated.

 

[SimoninParis]

The one which works when the second file is open but not when it is closed is:
=SOMME(INDIRECT.EXT("'D:/["&A5&".xls]feuil2'!$g$8:$g$27"))

You haven't shared what's in each of those cells that the formula uses.

You haven't shared your current version of the formula (the one including the
path).

Pete,

Thanks for your help. I've got it to give me the right answer BUT
INDIRECT.EXT still only works if the other file is open. What am I doing
wrong?

Simon,

you will need to include the full path to your file, something like
this:

=SOMME(INDIRECT.EXE("'C:/temp/["&A5&".xls]feuil2'!$g$8:$g$27"))

where I am assuming your files are in the folder C:/temp - change as
necessary.

Hope this helps.

Pete

On Sep 28, 1:10 pm, SimoninParis
On Friday, I asked a question and was given INDIRECT and/or INDIRECT.EXT as
possible solutions.

I finally managed to make INDIRECT work but I cannot make INDIRECT.EXT work.

My problem: 120 files with identical architecture - two worksheets each. I
would like to build a new file to give me certain data to be found in each of
the 120 files.

Each file is named after a person. In the new file column A will list each
person. I would like to put in column B:
=SOMME(INDIRECT("'["&A5&".xls]feuil2'!$g$8:$g$27"))
Please note that although I am English, my Excel is French.
This works if the named file is open. I would like to have a formula which
returns the correct information for closed files. If I substitute
INDIRECT.EXT for INDIRECT, it does not work. What is the required syntax?

If I use the "Formules" tab and click on IDIRECT.EXT in the "fonctions"
drop-down menu, it asks me for "Adresse, Volatile, Style, Mode"; I don't know
what to put where.

Any/all help will be greatly appreciated.

 

[Dave Peterson]

And what's in A5 of that sheet?

And did you try changing the / to \? I'm not sure if it matters to
=indirect.ext().

What happens when the formula recalculates--with the workbook open and with the
workbook closed????

The one which works when the second file is open but not when it is closed is:
=SOMME(INDIRECT.EXT("'D:/["&A5&".xls]feuil2'!$g$8:$g$27"))

You haven't shared what's in each of those cells that the formula uses.

You haven't shared your current version of the formula (the one including the
path).

Pete,

Thanks for your help. I've got it to give me the right answer BUT
INDIRECT.EXT still only works if the other file is open. What am I doing
wrong?

:

Simon,

you will need to include the full path to your file, something like
this:

=SOMME(INDIRECT.EXE("'C:/temp/["&A5&".xls]feuil2'!$g$8:$g$27"))

where I am assuming your files are in the folder C:/temp - change as
necessary.

Hope this helps.

Pete

On Sep 28, 1:10 pm, SimoninParis
On Friday, I asked a question and was given INDIRECT and/or INDIRECT.EXT as
possible solutions.

I finally managed to make INDIRECT work but I cannot make INDIRECT.EXT work.

My problem: 120 files with identical architecture - two worksheets each. I
would like to build a new file to give me certain data to be found in each of
the 120 files.

Each file is named after a person. In the new file column A will list each
person. I would like to put in column B:
=SOMME(INDIRECT("'["&A5&".xls]feuil2'!$g$8:$g$27"))
Please note that although I am English, my Excel is French.
This works if the named file is open. I would like to have a formula which
returns the correct information for closed files. If I substitute
INDIRECT.EXT for INDIRECT, it does not work. What is the required syntax?

If I use the "Formules" tab and click on IDIRECT.EXT in the "fonctions"
drop-down menu, it asks me for "Adresse, Volatile, Style, Mode"; I don't know
what to put where.

Any/all help will be greatly appreciated.

 

[SimoninParis]

Wow!

I think that's cracked it. I'm going to have a concerted effort now to
build the file I want.

Thanks a lot.

Simon

And what's in A5 of that sheet?

And did you try changing the / to \? I'm not sure if it matters to
=indirect.ext().

What happens when the formula recalculates--with the workbook open and with the
workbook closed????

The one which works when the second file is open but not when it is closed is:
=SOMME(INDIRECT.EXT("'D:/["&A5&".xls]feuil2'!$g$8:$g$27"))

You haven't shared what's in each of those cells that the formula uses.

You haven't shared your current version of the formula (the one including the
path).



SimoninParis wrote:

Pete,

Thanks for your help. I've got it to give me the right answer BUT
INDIRECT.EXT still only works if the other file is open. What am I doing
wrong?

:

Simon,

you will need to include the full path to your file, something like
this:

=SOMME(INDIRECT.EXE("'C:/temp/["&A5&".xls]feuil2'!$g$8:$g$27"))

where I am assuming your files are in the folder C:/temp - change as
necessary.

Hope this helps.

Pete

On Sep 28, 1:10 pm, SimoninParis
On Friday, I asked a question and was given INDIRECT and/or INDIRECT.EXT as
possible solutions.

I finally managed to make INDIRECT work but I cannot make INDIRECT.EXT work.

My problem: 120 files with identical architecture - two worksheets each. I
would like to build a new file to give me certain data to be found in each of
the 120 files.

Each file is named after a person. In the new file column A will list each
person. I would like to put in column B:
=SOMME(INDIRECT("'["&A5&".xls]feuil2'!$g$8:$g$27"))
Please note that although I am English, my Excel is French.
This works if the named file is open. I would like to have a formula which
returns the correct information for closed files. If I substitute
INDIRECT.EXT for INDIRECT, it does not work. What is the required syntax?

If I use the "Formules" tab and click on IDIRECT.EXT in the "fonctions"
drop-down menu, it asks me for "Adresse, Volatile, Style, Mode"; I don't know
what to put where.

Any/all help will be greatly appreciated.

 

[Jan]

Wow!

I think that's cracked it.  I'm going to have a concerted effort now to
build the file I want.

Thanks a lot.

Simon

And what's in A5 of that sheet?

And did you try changing the / to \?  I'm not sure if it matters to
=indirect.ext().

What happens when the formula recalculates--with the workbook open and with the
workbook closed????

SimoninParis wrote:

The one which works when the second file is open but not when it is closed is:
=SOMME(INDIRECT.EXT("'D:/["&A5&".xls]feuil2'!$g$8:$g$27"))
:
You haven't shared what's in each of those cells that the formula uses.
You haven't shared your current version of the formula (the one including the
path).
SimoninParis wrote:
Pete,
Thanks for your help. I've got it to give me the right answer BUT
INDIRECT.EXT still only works if the other file is open. What am I doing
wrong?
:
Simon,
you will need to include the full path to your file, something like
this:
=SOMME(INDIRECT.EXE("'C:/temp/["&A5&".xls]feuil2'!$g$8:$g$27"))
where I am assuming your files are in the folder C:/temp - change as
necessary.
Hope this helps.
Pete
On Sep 28, 1:10 pm, SimoninParis
On Friday, I asked a question and was given INDIRECT and/or INDIRECT.EXT as
possible solutions.
I finally managed to make INDIRECT work but I cannot make INDIRECT.EXT work.
My problem:  120 files with identical architecture - two worksheets each. I
would like to build a new file to give me certain data to be found in each of
the 120 files.
Each file is named after a person. In the new file column A will list each
person. I would like to put in column B:
=SOMME(INDIRECT("'["&A5&".xls]feuil2'!$g$8:$g$27"))
Please note that although I am English, my Excel is French.
This works if the named file is open. I would like to have a formula which
returns the correct information for closed files. If I substitute
INDIRECT.EXT  for INDIRECT, it does not work. What is the required syntax?
If I use the "Formules" tab and click on IDIRECT.EXT in the "fonctions"
drop-down menu, it asks me for "Adresse, Volatile, Style, Mode"; I don't know
what to put where.
Any/all help will be greatly appreciated.

--

Dave Peterson- Hide quoted text -

- Show quoted text -

I tried to download this file but could not download it. can you
please assist me with a copy?

 

[Dave Peterson]

If you're having trouble getting to the site, then search google for
indirect.ext.

I found this alternative site:
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

I didn't look to see if it was the most current version.

I'd check the original site every so often to see if it's working.