Formula Indirect with path

J

Juan Sanchez

Hi all:

Is there a function similar to INDIRECT, that will accept
not just a reference to a cell or range with in the
workbook, but actually a linked formula?

i.e

If I have in A1: 1000
and in B1: "Sheet1!A1" (as text with out the ")
and in C1:=Indirect(B1)

The result is C1: 1000, great it works but...
if in B1 instead of "Sheet1!A1", I have:

B1: 'c:\[MyBook.xls]Sheet1'!A1
The result in C1!!!: #REF!

Is there a way to do this?
The book exists, sheet1's name is sheet1 and cell A1 in
the book has a value...

Any Help is greatly appreciated...

Regards
Juan
 
P

Peter

Juan

The easiest way is to open MyBook then in the other
workbook type = and switch to the other book via the
windows menu and select the cell you want and press enter.

e.g =[OvertimeCalculation.xls]Sheet1!$E$10 works and excel
remembers the path. You will be asked if you wantto update
the links when the open the book with the formula.

Beware! do not change the location of the file or the
links will be lost.

Regards
Peter

(e-mail address removed)
 
G

Guest

Unfortunately, INDIRECT with a reference to another workbook only works when that workbook is open. Although I've heard there are some add-ins out there that have fixed this problem.
 
F

Frank Kabel

Hi
INDIRECT works only with open workbooks See the following
thread for
accessing closed workbooks (in your example INDIRECT.EXT
from the add-in morefunc.xll should work):
http://tinyurl.com/2c62u
 
J

Juan Sanchez

Thanks Frank! I'll check the site. Regards Juan

-----Original Message-----
Hi
INDIRECT works only with open workbooks See the following
thread for
accessing closed workbooks (in your example INDIRECT.EXT
from the add-in morefunc.xll should work):
http://tinyurl.com/2c62u

-----Original Message-----
Hi all:

Is there a function similar to INDIRECT, that will accept
not just a reference to a cell or range with in the
workbook, but actually a linked formula?

i.e

If I have in A1: 1000
and in B1: "Sheet1!A1" (as text with out the ")
and in C1:=Indirect(B1)

The result is C1: 1000, great it works but...
if in B1 instead of "Sheet1!A1", I have:

B1: 'c:\[MyBook.xls]Sheet1'!A1
The result in C1!!!: #REF!

Is there a way to do this?
The book exists, sheet1's name is sheet1 and cell A1 in
the book has a value...

Any Help is greatly appreciated...

Regards
Juan
.
Click to expand...
.
Click to expand...
 
J

Juan Sanchez

Thanks Peter, unfortunatelly it is not just one workbook,
and my pc is not very gifted in memory (either,am I :) )as
to have them all open, Regards... Juan

-----Original Message-----
Juan

The easiest way is to open MyBook then in the other
workbook type = and switch to the other book via the
windows menu and select the cell you want and press enter.

e.g =[OvertimeCalculation.xls]Sheet1!$E$10 works and excel
remembers the path. You will be asked if you wantto update
the links when the open the book with the formula.

Beware! do not change the location of the file or the
links will be lost.

Regards
Peter

(e-mail address removed)
-----Original Message-----
Hi all:

Is there a function similar to INDIRECT, that will accept
not just a reference to a cell or range with in the
workbook, but actually a linked formula?

i.e

If I have in A1: 1000
and in B1: "Sheet1!A1" (as text with out the ")
and in C1:=Indirect(B1)

The result is C1: 1000, great it works but...
if in B1 instead of "Sheet1!A1", I have:

B1: 'c:\[MyBook.xls]Sheet1'!A1
The result in C1!!!: #REF!

Is there a way to do this?
The book exists, sheet1's name is sheet1 and cell A1 in
the book has a value...

Any Help is greatly appreciated...

Regards
Juan
.
Click to expand...
.
Click to expand...
 
J

Juan Sanchez

0013, Thanks, Frank Kabel has pointed me to the add-in
you'r talking avout... Regards, Juan

-----Original Message-----
Unfortunately, INDIRECT with a reference to another
Click to expand...
workbook only works when that workbook is open. Although
I've heard there are some add-ins out there that have
fixed this problem.
Juan Sanchez said:
Hi all:

Is there a function similar to INDIRECT, that will accept
not just a reference to a cell or range with in the
workbook, but actually a linked formula?

i.e

If I have in A1: 1000
and in B1: "Sheet1!A1" (as text with out the ")
and in C1:=Indirect(B1)

The result is C1: 1000, great it works but...
if in B1 instead of "Sheet1!A1", I have:

B1: 'c:\[MyBook.xls]Sheet1'!A1
The result in C1!!!: #REF!

Is there a way to do this?
The book exists, sheet1's name is sheet1 and cell A1 in
the book has a value...

Any Help is greatly appreciated...

Regards
Juan
Click to expand...
.
Click to expand...
 
C

Crugers

Hi Juan
I too thought INDIRECT() would solve my problems but..
KB213933 states that :
NOTE: The INDIRECT function only returns the result of a
reference to an open file. If a workbook that the INDIRECT
function is indirectly referencing is closed, the function
returns a #REF! error.

Workaround could be to use VBA to open linked file(s)...
However once the linked file is closed again, and the
sheet where INDIRECT() is used is recalculated, it reverts
to #REF...
Sorry not much help but a little empathy may help ;-)
Mick
-----Original Message-----
Juan

The easiest way is to open MyBook then in the other
workbook type = and switch to the other book via the
windows menu and select the cell you want and press enter.

e.g =[OvertimeCalculation.xls]Sheet1!$E$10 works and excel
remembers the path. You will be asked if you wantto update
the links when the open the book with the formula.

Beware! do not change the location of the file or the
links will be lost.

Regards
Peter

(e-mail address removed)
-----Original Message-----
Hi all:

Is there a function similar to INDIRECT, that will accept
not just a reference to a cell or range with in the
workbook, but actually a linked formula?

i.e

If I have in A1: 1000
and in B1: "Sheet1!A1" (as text with out the ")
and in C1:=Indirect(B1)

The result is C1: 1000, great it works but...
if in B1 instead of "Sheet1!A1", I have:

B1: 'c:\[MyBook.xls]Sheet1'!A1
The result in C1!!!: #REF!

Is there a way to do this?
The book exists, sheet1's name is sheet1 and cell A1 in
the book has a value...

Any Help is greatly appreciated...

Regards
Juan
.
Click to expand...
.
Click to expand...
 
H

Harlan Grove

Crugers said:
I too thought INDIRECT() would solve my problems but..
KB213933 states that :
NOTE: The INDIRECT function only returns the result of a
reference to an open file. If a workbook that the INDIRECT
function is indirectly referencing is closed, the function
returns a #REF! error.

Workaround could be to use VBA to open linked file(s)...
However once the linked file is closed again, and the
sheet where INDIRECT() is used is recalculated, it reverts
to #REF...
Click to expand...
....

The KB article was written by people without much imagination. VBA is needed
for one possible solution, but opening all linked files is clumsy brute
force and unnecessary (though it *is* the best approach when there are
*MANY* links into *FEW* workbooks, thus giving a recalc performance gain
that offsets the necessary memory consumption).

See the following for other alternatives.

http://groups.google.com/[email protected]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

concatenate 2
Indirect used in an array formula 3
Vlookup and Indirect 0
Variable reference in a function 3
sumproduct with indirect 5
Indirect another wookbook 8
INDIRECT FUNCTION jams? 3
sum() using indirect() 3

Top