reference a closed filed from a cell and formula in an open file

[dh13134]

scenario:
file ABC.xls has the number 5 in A1 (a closed file)
file XYZ.xls has the number 10 in A1 (a closed file)

file choose.xls (an open file) has EITHER ABC or XYZ in cell A1 and ~some
formula in A2

i need a formula for cell A2 (choose.xls) that will display EITHER 5 or 10
in A2, depending on what is in cell A1 (EITHER ABC or XYZ)??

thanks

 

[Pete_UK]

You would normally use INDIRECT to do this. However, it does not work
with closed files.

There is a free add-in, morefunc, which contains the function
INDIRECT.EXT - this is meant to work with closed files, so perhaps you
can download that and give it a try.

Hope this helps.

Pete

 

[dh13134]

i've tried for the past 45 minutes and can't seem to get it to do what i
want. it DOES reference closed files, but varying the formula based on
another cell value still doesn't work out.
thanks

any other suggestions would be welcome....

 

[Barb Reinhardt]

I've used INDIRECT_EXT and it appears to open the file each time the function
is calculated. I had a bunch of calculations accessing a file over a network
so that wasn't the most efficient way to do things. It did get be started
thinking about macros though.

Barb Reinhardt

 

[Barb Reinhardt]

You've got to get the formulas just right.

What exactly are you trying to do? Give an example of the cell ID and
what's in it

Thanks,
Barb Reinhardt

 

[Pete_UK]

Well, I don't use it so I can't comment on it, but there have been
favourable comments on other threads. Another common reference to this
type of problem is Harlan Grove's Pull function - I'm sure you'll find
references to it if you do a Google search of the Excel groups.

Hope this helps.

Pete

 

[dh13134]

i need the text contents of a cell to be used in a formula (one the same
sheet), to return the contents of a closed file....sooo...in my earlier
example, my "formula" will display the contents of one of my 2 files,
depending on the TEXT in cell A1.

i can get the structure to work how it is designed to work, but i can't get
the formula to reflect a change when i change cell A1 - i have to actually
change the formula ITSELF to include "ABC" or "XYZ" in the formula. i don't
want to change the formula itself, i want the formula to be changed based on
what is in my other cell.

simplified (hopefully):
if choose.xls cell A1 = ABC, then cell A2 would be ~~=ABC.xls!A1 (5)
if choose.xls cell A1 = XYZ, then cell A2 would be ~~=XYZ.xls!A1 (10)

thanks

 

[Peo Sjoblom]

You need to reference a sheet name as well. if ABC is the file name then you
need a sheet name.

--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

Plus the whole path

=INDIRECT.EXT("'C:\Documents and
Settings\psjoblom\Desktop\["&A1&".xls]Sheet1'!A1")

using my desktop



--


Regards,


Peo Sjoblom

 

[dh13134]

IT WORKS!!! Thanks very much for you help!! i've been working on this off
and on for months.
CASE CLOSED!!
--
dh13134

Plus the whole path

=INDIRECT.EXT("'C:\Documents and
Settings\psjoblom\Desktop\["&A1&".xls]Sheet1'!A1")

using my desktop



--


Regards,


Peo Sjoblom

 

[dh13134]

before closing......

do you know if this works if i had ABC.csv and XYZ.csv?? i quickly tried to
substitute .csv in place of .xls and it didn't work.....maybe some slightly
different format?...
--
dh13134

Plus the whole path

=INDIRECT.EXT("'C:\Documents and
Settings\psjoblom\Desktop\["&A1&".xls]Sheet1'!A1")

using my desktop



--


Regards,


Peo Sjoblom

 

[Pete_UK]

Glad to hear it. It's always a good idea to post the formulae that you
have tried, so that we are not guessing what you might have done.

Pete

 

[Peo Sjoblom]

Nope. I don't think it will work with a csv file

--


Regards,


Peo Sjoblom

before closing......

do you know if this works if i had ABC.csv and XYZ.csv?? i quickly tried
to
substitute .csv in place of .xls and it didn't work.....maybe some
slightly
different format?...
--
dh13134

Plus the whole path

=INDIRECT.EXT("'C:\Documents and
Settings\psjoblom\Desktop\["&A1&".xls]Sheet1'!A1")

using my desktop



--


Regards,


Peo Sjoblom

You need to reference a sheet name as well. if ABC is the file name
then
you need a sheet name.

--


Regards,


Peo Sjoblom

i need the text contents of a cell to be used in a formula (one the
same
sheet), to return the contents of a closed file....sooo...in my
earlier
example, my "formula" will display the contents of one of my 2 files,
depending on the TEXT in cell A1.

i can get the structure to work how it is designed to work, but i
can't
get
the formula to reflect a change when i change cell A1 - i have to
actually
change the formula ITSELF to include "ABC" or "XYZ" in the formula. i
don't
want to change the formula itself, i want the formula to be changed
based
on
what is in my other cell.

simplified (hopefully):
if choose.xls cell A1 = ABC, then cell A2 would be ~~=ABC.xls!A1 (5)
if choose.xls cell A1 = XYZ, then cell A2 would be ~~=XYZ.xls!A1 (10)

thanks



--
dh13134


:

You've got to get the formulas just right.

What exactly are you trying to do? Give an example of the cell ID
and
what's in it

Thanks,
Barb Reinhardt



:

i've tried for the past 45 minutes and can't seem to get it to do
what
i
want. it DOES reference closed files, but varying the formula based
on
another cell value still doesn't work out.
thanks

any other suggestions would be welcome....
--
dh13134


:

You would normally use INDIRECT to do this. However, it does not
work
with closed files.

There is a free add-in, morefunc, which contains the function
INDIRECT.EXT - this is meant to work with closed files, so
perhaps
you
can download that and give it a try.

Hope this helps.

Pete

On Aug 5, 10:33 pm, dh13134 <[email protected]>
wrote:
scenario:
file ABC.xls has the number 5 in A1 (a closed file)
file XYZ.xls has the number 10 in A1 (a closed file)

file choose.xls (an open file) has EITHER ABC or XYZ in cell A1
and ~some
formula in A2

i need a formula for cell A2 (choose.xls) that will display
EITHER
5 or 10
in A2, depending on what is in cell A1 (EITHER ABC or XYZ)??

thanks

 

[dh13134]

good idea:

recall my scenario:
file ABC.xls has the number 5 in A1 (a closed file)
file XYZ.xls has the number 10 in A1 (a closed file)

file choose.xls (an open file) has EITHER ABC or XYZ in cell A1 and ~some
formula in A2

i need a formula for cell A2 (choose.xls) that will display EITHER 5 or 10
in A2, depending on what is in cell A1 (EITHER ABC or XYZ)??

ANSWER: cell A2 is =INDIRECT.EXT("'C:\TEMP\["&A1&".xls]SHEET1'!A1"). in
choose.xls, when cell A1 = ABC, then cell A2 = 5. when cell B1 = XYZ, then
cell A2 = 10.

thanks again for the help!
--
dh13134

Glad to hear it. It's always a good idea to post the formulae that you
have tried, so that we are not guessing what you might have done.

Pete

 

[Barb Reinhardt]

I'm guessing the code for Indirect_Ext opens an Excel workbook, not a CSV
file. As I write this, I'm wondering if it will work for Excel 2007 files
as well.

Barb Reinhardt

before closing......

do you know if this works if i had ABC.csv and XYZ.csv?? i quickly tried to
substitute .csv in place of .xls and it didn't work.....maybe some slightly
different format?...
--
dh13134

Plus the whole path

=INDIRECT.EXT("'C:\Documents and
Settings\psjoblom\Desktop\["&A1&".xls]Sheet1'!A1")

using my desktop



--


Regards,


Peo Sjoblom

You need to reference a sheet name as well. if ABC is the file name then
you need a sheet name.

--


Regards,


Peo Sjoblom

i need the text contents of a cell to be used in a formula (one the same
sheet), to return the contents of a closed file....sooo...in my earlier
example, my "formula" will display the contents of one of my 2 files,
depending on the TEXT in cell A1.

i can get the structure to work how it is designed to work, but i can't
get
the formula to reflect a change when i change cell A1 - i have to
actually
change the formula ITSELF to include "ABC" or "XYZ" in the formula. i
don't
want to change the formula itself, i want the formula to be changed based
on
what is in my other cell.

simplified (hopefully):
if choose.xls cell A1 = ABC, then cell A2 would be ~~=ABC.xls!A1 (5)
if choose.xls cell A1 = XYZ, then cell A2 would be ~~=XYZ.xls!A1 (10)

thanks



--
dh13134


:

You've got to get the formulas just right.

What exactly are you trying to do? Give an example of the cell ID and
what's in it

Thanks,
Barb Reinhardt



:

i've tried for the past 45 minutes and can't seem to get it to do what
i
want. it DOES reference closed files, but varying the formula based on
another cell value still doesn't work out.
thanks

any other suggestions would be welcome....
--
dh13134


:

You would normally use INDIRECT to do this. However, it does not
work
with closed files.

There is a free add-in, morefunc, which contains the function
INDIRECT.EXT - this is meant to work with closed files, so perhaps
you
can download that and give it a try.

Hope this helps.

Pete

On Aug 5, 10:33 pm, dh13134 <[email protected]>
wrote:
scenario:
file ABC.xls has the number 5 in A1 (a closed file)
file XYZ.xls has the number 10 in A1 (a closed file)

file choose.xls (an open file) has EITHER ABC or XYZ in cell A1
and ~some
formula in A2

i need a formula for cell A2 (choose.xls) that will display EITHER
5 or 10
in A2, depending on what is in cell A1 (EITHER ABC or XYZ)??

thanks

 

[Peo Sjoblom]

The latest version should be compatible with 2007

--


Regards,


Peo Sjoblom

I'm guessing the code for Indirect_Ext opens an Excel workbook, not a CSV
file. As I write this, I'm wondering if it will work for Excel 2007
files
as well.

Barb Reinhardt

before closing......

do you know if this works if i had ABC.csv and XYZ.csv?? i quickly tried
to
substitute .csv in place of .xls and it didn't work.....maybe some
slightly
different format?...
--
dh13134

Plus the whole path

=INDIRECT.EXT("'C:\Documents and
Settings\psjoblom\Desktop\["&A1&".xls]Sheet1'!A1")

using my desktop



--


Regards,


Peo Sjoblom

You need to reference a sheet name as well. if ABC is the file name
then
you need a sheet name.

--


Regards,


Peo Sjoblom

i need the text contents of a cell to be used in a formula (one the
same
sheet), to return the contents of a closed file....sooo...in my
earlier
example, my "formula" will display the contents of one of my 2
files,
depending on the TEXT in cell A1.

i can get the structure to work how it is designed to work, but i
can't
get
the formula to reflect a change when i change cell A1 - i have to
actually
change the formula ITSELF to include "ABC" or "XYZ" in the formula.
i
don't
want to change the formula itself, i want the formula to be changed
based
on
what is in my other cell.

simplified (hopefully):
if choose.xls cell A1 = ABC, then cell A2 would be ~~=ABC.xls!A1 (5)
if choose.xls cell A1 = XYZ, then cell A2 would be ~~=XYZ.xls!A1
(10)

thanks



--
dh13134


:

You've got to get the formulas just right.

What exactly are you trying to do? Give an example of the cell ID
and
what's in it

Thanks,
Barb Reinhardt



:

i've tried for the past 45 minutes and can't seem to get it to do
what
i
want. it DOES reference closed files, but varying the formula
based on
another cell value still doesn't work out.
thanks

any other suggestions would be welcome....
--
dh13134


:

You would normally use INDIRECT to do this. However, it does
not
work
with closed files.

There is a free add-in, morefunc, which contains the function
INDIRECT.EXT - this is meant to work with closed files, so
perhaps
you
can download that and give it a try.

Hope this helps.

Pete

On Aug 5, 10:33 pm, dh13134 <[email protected]>
wrote:
scenario:
file ABC.xls has the number 5 in A1 (a closed file)
file XYZ.xls has the number 10 in A1 (a closed file)

file choose.xls (an open file) has EITHER ABC or XYZ in cell
A1
and ~some
formula in A2

i need a formula for cell A2 (choose.xls) that will display
EITHER
5 or 10
in A2, depending on what is in cell A1 (EITHER ABC or XYZ)??

thanks