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

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

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

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


"Pete_UK" wrote:

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

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



"Pete_UK" wrote:

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

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



"dh13134" wrote:

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


"Pete_UK" wrote:

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

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

On Aug 5, 11:28*pm, dh13134 wrote:
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

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


"Barb Reinhardt" wrote:

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



"dh13134" wrote:

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


"Pete_UK" wrote:

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

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

--


Regards,


Peo Sjoblom

"dh13134" wrote in message
...
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


"Barb Reinhardt" wrote:

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



"dh13134" wrote:

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


"Pete_UK" wrote:

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

Plus the whole path

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

using my desktop



--


Regards,


Peo Sjoblom

"Peo Sjoblom" wrote in message
...
You need to reference a sheet name as well. if ABC is the file name then
you need a sheet name.

--


Regards,


Peo Sjoblom

"dh13134" wrote in message
...
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


"Barb Reinhardt" wrote:

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



"dh13134" wrote:

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


"Pete_UK" wrote:

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

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


"Peo Sjoblom" wrote:

Plus the whole path

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

using my desktop



--


Regards,


Peo Sjoblom

"Peo Sjoblom" wrote in message
...
You need to reference a sheet name as well. if ABC is the file name then
you need a sheet name.

--


Regards,


Peo Sjoblom

"dh13134" wrote in message
...
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


"Barb Reinhardt" wrote:

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



"dh13134" wrote:

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


"Pete_UK" wrote:

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