Indirect references to an offline book

  • Thread starter Thread starter ismae
  • Start date Start date
I

ismae

Hi everyone!
I'm trying to make references from one book to another by using the INDIRECT
function, but I found out it doesn't work when the source book is not open.
Making the same reference without the INDIRECT function works ok.
The purpose of my application is to sum data from several books into one
summary book, but the names and quantity of books can vary dynamically, so I
can't include them in my formulas.
Can anyone help me with this?
 
You can download an add-in called Morefunc it has a function called
INDIRECT.EXT
that will work if the source book is closed

http://xcell05.free.fr/morefunc/english/index.htm


there is no built in function that can take a string referring to another
workbook that is not open and make it into a valid formula




--


Regards,


Peo Sjoblom
 
Thank you for your answer, Peo. I'm going to try that, although I would
prefer some solution which doesn't involve something being installed, because
I need to distribute my workbooks among many people who might not be able to
do it.
 
There is no other way really using functions and you can actually embed the
function if you distribute workbooks based on it, here's a snippet from
help:


"Embedding Morefunc in a workbook has the following consequences :

· It adds a "very hidden" worksheet ("Morefunc Storage Sheet") to the
workbook. The add-in itself and the help file are stored in this sheet as
binary data.

· It adds a small standard module named modRestoreMorefunc to the VBA
project of the workbook.

· It inserts a call to the MorefuncTempInstall Sub in the Workbook_Open
event handler of the workbook.

None of these 3 items should be removed or altered, otherwise the new
functions won't work.

When the workbook is opened, the MorefuncTempInstall sub performs these
tasks :


· It checks if Morefunc is already installed (and loaded) in the current
Excel instance

· If Morefunc is already loaded, it compares its version number with the one
of the Morefunc add-in stored in the workbook.

· If the version of the workbook is more recent (or if Morefunc is not
installed), it reads the binary data stored in the hidden sheet, creates a
Morefunc.xll file in the temporary folder and opens it."



--


Regards,


Peo Sjoblom
 
hi Peo,

i also have the same problem so i addin the Morefunc Addin to my workbook with 2 worksheets, both indirect.ext worked for both work sheets but when i saved and reopen the workbook, only one of the worksheet works. the other return #ref error.

What actually went wrong?



EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com
 
I am not an expert on Morefunc but my guess is that you have calculation set
to manual in that particular sheet


--


Regards,


Peo Sjoblom
 
Sorry, but INDIRECT.EXT does not work. I installed the morefunc file and it
has the same limitation - it only returns the correct value when the source
workbook is open. If you close it, it returns 0.
 
Worked for me when I tested it, so did Harlan Grove's Pull function

ftp://members.aol.com/hrlngrv/pull.zip


--


Regards,


Peo Sjoblom
 
Maybe someone can tell me then why this doesn't work:

Cell E2 = 2
Cell F2 = 3
Cell G2 = 4 etc. through L2 = 9
Cell E3 =
''G:\GS_Secure\REPORTS\Monthly\PRODSVC\CURRENT\[PS-01_OCT.xls]Sheet1'!$C$2:$L$171
(those are two single quotes at the beginning)

Cell E5 =
=IF(ISNA(VLOOKUP($D5,INDIRECT.EXT($E$2),E$1,FALSE)),"",VLOOKUP($D5,INDIRECT.EXT($E$2),E$1,FALSE))

When the PS-01_OCT.xls workbook is open, everything is fine, when I close
it, all cells turn to #REF!

Therefore, INDRECT.EXT does not work on closed books. The "pull" function
you mentioned also does the same (already tried that before trying
INDIRECT.EXE).

Ideas?
 
Robert_L_Ross said:
Therefore, INDRECT.EXT does not work on closed books. The "pull"
function you mentioned also does the same (already tried that before
trying INDIRECT.EXE).
Click to expand...
....

INDIRECT.EXT doesn't work on some systems, but pull is usually more
robust. For example, I open a new workbook and enter the following
starting in Sheet1!A1.

-1E+307 before
2 first
3 second
5 third
7 fourth
11 fifth
13 sixth
17 seventh
19 eighth
=A9+10^INT(LOG10(A9)-14) after

I save that file as C:\temp\test.xls and close it. I open another new
workbook and enter the following starting in Sheet1!A1.

C:
\temp\
test.xls
Sheet1
A1:B10
4
="'"&A1&A2&"["&A3&"]"&A4&"'!"&A5
=VLOOKUP(A6,pull(A7),2)

On my system, the formula in A8 returns second . When I change A6 to
12, A8 returns fifth .

The following is a guess: iF you happen to have Office group policy
settings that prevent running multiple instances of Excel, neither
INDIRECT.EXT or pull will work because they both rely on using
secondary hidden instances of Excel to fetch data from closed
workbooks using the ExecuteExcel4Macro method of Excel's Application
class.

Your only other alternative is using SQL.REQUEST or macros to change
literal external references in formulas containing them.
 
Remove the preceding quotes and apostrophe from the cell and try

IF(ISNA(VLOOKUP($D5,INDIRECT.EXT("'"&$E$2),E$1,FALSE)),"",VLOOKUP($D5,INDIRECT.EXT("'"&$E$2),E$1,FALSE))


that worked on my network at work. If it doesn't then it might be a company
policy thing
since I tried with 2 closed workbooks, one that was on my C drive and one
that was on a network drive and they both worked as long as I removed the
apostrophe and the leading quote and appended them to the formula itself
instead



--


Regards,


Peo Sjoblom








Robert_L_Ross said:
Maybe someone can tell me then why this doesn't work:

Cell E2 = 2
Cell F2 = 3
Cell G2 = 4 etc. through L2 = 9
Cell E3 =
''G:\GS_Secure\REPORTS\Monthly\PRODSVC\CURRENT\[PS-01_OCT.xls]Sheet1'!$C$2:$L$171
(those are two single quotes at the beginning)

Cell E5 =
=IF(ISNA(VLOOKUP($D5,INDIRECT.EXT($E$2),E$1,FALSE)),"",VLOOKUP($D5,INDIRECT.EXT($E$2),E$1,FALSE))

When the PS-01_OCT.xls workbook is open, everything is fine, when I close
it, all cells turn to #REF!

Therefore, INDRECT.EXT does not work on closed books. The "pull" function
you mentioned also does the same (already tried that before trying
INDIRECT.EXE).

Ideas?


Peo Sjoblom said:
Worked for me when I tested it, so did Harlan Grove's Pull function

ftp://members.aol.com/hrlngrv/pull.zip


--


Regards,


Peo Sjoblom
Click to expand...
Click to expand...
 
Per my company we have no restrictions on our policies that would cause this
to hang. I removed the apostrphe's and used your code exactly and it still
shows #ref! unless the book is open.

Therefore, INDIRECT.EXT is unreliable and another solution should be used.

Peo Sjoblom said:
Remove the preceding quotes and apostrophe from the cell and try

IF(ISNA(VLOOKUP($D5,INDIRECT.EXT("'"&$E$2),E$1,FALSE)),"",VLOOKUP($D5,INDIRECT.EXT("'"&$E$2),E$1,FALSE))


that worked on my network at work. If it doesn't then it might be a company
policy thing
since I tried with 2 closed workbooks, one that was on my C drive and one
that was on a network drive and they both worked as long as I removed the
apostrophe and the leading quote and appended them to the formula itself
instead



--


Regards,


Peo Sjoblom








Robert_L_Ross said:
Maybe someone can tell me then why this doesn't work:

Cell E2 = 2
Cell F2 = 3
Cell G2 = 4 etc. through L2 = 9
Cell E3 =
''G:\GS_Secure\REPORTS\Monthly\PRODSVC\CURRENT\[PS-01_OCT.xls]Sheet1'!$C$2:$L$171
(those are two single quotes at the beginning)

Cell E5 =
=IF(ISNA(VLOOKUP($D5,INDIRECT.EXT($E$2),E$1,FALSE)),"",VLOOKUP($D5,INDIRECT.EXT($E$2),E$1,FALSE))

When the PS-01_OCT.xls workbook is open, everything is fine, when I close
it, all cells turn to #REF!

Therefore, INDRECT.EXT does not work on closed books. The "pull" function
you mentioned also does the same (already tried that before trying
INDIRECT.EXE).

Ideas?


Peo Sjoblom said:
Worked for me when I tested it, so did Harlan Grove's Pull function

ftp://members.aol.com/hrlngrv/pull.zip


--


Regards,


Peo Sjoblom


Sorry, but INDIRECT.EXT does not work. I installed the morefunc file
and
it
has the same limitation - it only returns the correct value when the
source
workbook is open. If you close it, it returns 0.

:

You can download an add-in called Morefunc it has a function called
INDIRECT.EXT
that will work if the source book is closed

http://xcell05.free.fr/morefunc/english/index.htm


there is no built in function that can take a string referring to
another
workbook that is not open and make it into a valid formula




--


Regards,


Peo Sjoblom


Hi everyone!
I'm trying to make references from one book to another by using the
INDIRECT
function, but I found out it doesn't work when the source book is
not
open.
Making the same reference without the INDIRECT function works ok.
The purpose of my application is to sum data from several books into
one
summary book, but the names and quantity of books can vary
dynamically,
so
I
can't include them in my formulas.
Can anyone help me with this?
Click to expand...
Click to expand...
Click to expand...
 
It appears as though INDIRECT.EXT doesn't work in conjunction when used
within specific functions. For example, Peo says it works fine on his
system, yet it doesn't work on ours (even though we have confirmed there are
no policy restrictions that would cause this to fail). Yet, when I use
INDIRECT.EXT this way:
Cell F148:
=IF(ISNUMBER(INDIRECT.EXT(S148)),INDIRECT.EXT(S148),"")
Cell S148:
''G:\GS_Secure\REPORTS\Monthly\HiPath\USER_SUMMARY\10\[USER.xls]Sheet1'!$B$3
(two apostrphe's at the beginning)
it works fine.

It appears to become unstable when using it in something complex like
VLookup, but it can be evaluated using a simple IsNumber.

Sure wish PULL worked. Either way, they are both incredibly slow when using
a lot of them in spreadsheets (every time I try to update one, I have to wait
a good 10 seconds for cells to update).

Maybe microsoft can take this into account for their next release.

Peo Sjoblom said:
Remove the preceding quotes and apostrophe from the cell and try

IF(ISNA(VLOOKUP($D5,INDIRECT.EXT("'"&$E$2),E$1,FALSE)),"",VLOOKUP($D5,INDIRECT.EXT("'"&$E$2),E$1,FALSE))


that worked on my network at work. If it doesn't then it might be a company
policy thing
since I tried with 2 closed workbooks, one that was on my C drive and one
that was on a network drive and they both worked as long as I removed the
apostrophe and the leading quote and appended them to the formula itself
instead



--


Regards,


Peo Sjoblom








Robert_L_Ross said:
Maybe someone can tell me then why this doesn't work:

Cell E2 = 2
Cell F2 = 3
Cell G2 = 4 etc. through L2 = 9
Cell E3 =
''G:\GS_Secure\REPORTS\Monthly\PRODSVC\CURRENT\[PS-01_OCT.xls]Sheet1'!$C$2:$L$171
(those are two single quotes at the beginning)

Cell E5 =
=IF(ISNA(VLOOKUP($D5,INDIRECT.EXT($E$2),E$1,FALSE)),"",VLOOKUP($D5,INDIRECT.EXT($E$2),E$1,FALSE))

When the PS-01_OCT.xls workbook is open, everything is fine, when I close
it, all cells turn to #REF!

Therefore, INDRECT.EXT does not work on closed books. The "pull" function
you mentioned also does the same (already tried that before trying
INDIRECT.EXE).

Ideas?


Peo Sjoblom said:
Worked for me when I tested it, so did Harlan Grove's Pull function

ftp://members.aol.com/hrlngrv/pull.zip


--


Regards,


Peo Sjoblom


Sorry, but INDIRECT.EXT does not work. I installed the morefunc file
and
it
has the same limitation - it only returns the correct value when the
source
workbook is open. If you close it, it returns 0.

:

You can download an add-in called Morefunc it has a function called
INDIRECT.EXT
that will work if the source book is closed

http://xcell05.free.fr/morefunc/english/index.htm


there is no built in function that can take a string referring to
another
workbook that is not open and make it into a valid formula




--


Regards,


Peo Sjoblom


Hi everyone!
I'm trying to make references from one book to another by using the
INDIRECT
function, but I found out it doesn't work when the source book is
not
open.
Making the same reference without the INDIRECT function works ok.
The purpose of my application is to sum data from several books into
one
summary book, but the names and quantity of books can vary
dynamically,
so
I
can't include them in my formulas.
Can anyone help me with this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top