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

  • Thread starter Thread starter dh13134
  • Start date Start date
D

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
 
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
 
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....
 
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
 
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
 
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
 
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
 
You need to reference a sheet name as well. if ABC is the file name then you
need a sheet name.

--


Regards,


Peo Sjoblom
 
Plus the whole path

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

using my desktop



--


Regards,


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


Peo Sjoblom said:
Plus the whole path

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

using my desktop



--


Regards,


Peo Sjoblom
Click to expand...
 
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


Peo Sjoblom said:
Plus the whole path

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

using my desktop



--


Regards,


Peo Sjoblom
Click to expand...
 
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
 
Nope. I don't think it will work with a csv file

--


Regards,


Peo Sjoblom

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


Peo Sjoblom said:
Plus the whole path

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

using my desktop



--


Regards,


Peo Sjoblom

Peo Sjoblom said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Pete_UK said:
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
Click to expand...
 
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



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


Peo Sjoblom said:
Plus the whole path

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

using my desktop



--


Regards,


Peo Sjoblom

Peo Sjoblom said:
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
Click to expand...
Click to expand...
Click to expand...
 
The latest version should be compatible with 2007

--


Regards,


Peo Sjoblom

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



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


Peo Sjoblom said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top