INDIRECT.EXT Morefunc.xll addin

J

John R

New Member
Joined
May 19, 2004
Messages
16
Has any anyone had success with using this addin?
I have tried it and if the woorkbook is open it works fine just like INDIRECT but if the workbook is closed I get a #REF! error.
Any help or comments would be appreciated.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Hi, hello and welcome to MrExcel -- yes, there are ailures with that on some O/S and version combinations.

Try the PULL function by Harlan Grove --

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> pull(xref <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
<SPAN style="color:#007F00">'inspired by Bob Phillips and Laurent Longre</SPAN>
<SPAN style="color:#007F00">'but written by Harlan Grove</SPAN>
<SPAN style="color:#007F00">'-----------------------------------------------------------------</SPAN>
<SPAN style="color:#007F00">'Copyright (c) 2003 Harlan Grove.</SPAN>
<SPAN style="color:#007F00">'</SPAN>
<SPAN style="color:#007F00">'This code is free software; you can redistribute it and/or modify</SPAN>
<SPAN style="color:#007F00">'it under the terms of the GNU General Public License as published</SPAN>
<SPAN style="color:#007F00">'by the Free Software Foundation; either version 2 of the License,</SPAN>
<SPAN style="color:#007F00">'or (at your option) any later version.</SPAN>
<SPAN style="color:#007F00">'-----------------------------------------------------------------</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> xlapp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, xlwb <SPAN style="color:#00007F">As</SPAN> Workbook
<SPAN style="color:#00007F">Dim</SPAN> b <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, r <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range, n <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>

pull = Evaluate(xref)

<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">CStr</SPAN>(pull) = <SPAN style="color:#00007F">CStr</SPAN>(CVErr(xlErrRef)) <SPAN style="color:#00007F">Then</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> CleanUp <SPAN style="color:#007F00">'immediate clean-up at this point</SPAN>

<SPAN style="color:#00007F">Set</SPAN> xlapp = CreateObject("Excel.Application")
<SPAN style="color:#00007F">Set</SPAN> xlwb = xlapp.Workbooks.Add <SPAN style="color:#007F00">'needed by .ExecuteExcel4Macro</SPAN>

<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN> <SPAN style="color:#007F00">'now clean-up can wait</SPAN>

n = InStr(<SPAN style="color:#00007F">In</SPAN>Str(1, xref, "]") + 1, xref, "!")
b = Mid(xref, 1, n)

<SPAN style="color:#00007F">Set</SPAN> r = xlwb.Sheets(1).Range(Mid(xref, n + 1))

<SPAN style="color:#00007F">If</SPAN> r <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
pull = xlapp.ExecuteExcel4Macro(xref)
<SPAN style="color:#00007F">Else</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c In r
c.Value = xlapp.ExecuteExcel4Macro(b & c.Address(1, 1, xlR1C1))
<SPAN style="color:#00007F">Next</SPAN> c

pull = r.Value
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

CleanUp:
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> xlwb <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> xlwb.Close 0
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> xlapp <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> xlapp.Quit
<SPAN style="color:#00007F">Set</SPAN> xlapp = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

ex SUM(pull("'"&A1&"\["&B1&".xls]"&C1&"'!"&"e1:e10"))

A1:
C:\Documents and Settings\My Documents

B1:
money2

C1:
sheet1
 
Upvote 0
It would be useful if you posted your formula. In the meantime, here are a few general comments:

INDIRECT.EXT does not work with
• PC’s running Windows Me (Millennium version)
• defined names in closed workbooks.
• Range arguments (it can only return single values)

References:
http://makeashorterlink.com/?O2E512068
http://makeashorterlink.com/?H21621068

In addition, you cannot copy a formula containing INDIRECT.EXT by dragging the formula down/across a range, unless you make a slight adjustment to the syntax e.g. you wish to get data from say cells A1, A2 and A3 from a closed file named Test1.xls, located at C:\Temp3:

B4:
=INDIRECT.EXT("'C:\Temp3\[Test1.xls]Sheet3'!A"&C4)
drag the formula down.

C4 contains 1, C5 contains 2 and C6 contains 3.

Some alternatives to INDIRECT.EXT at http://makeashorterlink.com/?T1D723297 .
The reference also includes an example on how to use INDIRECT.EXT in conjunction with the Sumproduct function (not tested).

Other alternatives here:http://www.mrexcel.com/board2/viewtopic.php?t=67212
Nimrod’s macro works very well.

Other notes:

If you just want to link to a closed workbook/worksheet, you can simply use:

='C:\Temp3\[Test1.xls]sheet1'!A1

If you are doing lookups, Vlookup/HLookup and Index/Match work with closed workbooks (you do not have to use INDIRECT.EXT).

Harlan Grove’s “Pull” function works very well (I use this in a few applications): see Jon's post above.

Warning:
Do not try to drag Mr. Grove’s UDF formula down or across the page. On my PC, Excel “freezes” and requires Ctrl-Alt-Delete to get out of it. Similarly, in one application I have a validation dropdown that houses file names of closed workbooks. If I selected a file name that does not exist, the UDF went into an endless loop. In this case, only a hard re-boot worked (Ctrl-Alt-Delete failed).

Regards,


Mike
 
Upvote 0
Ekim said:
It would be useful if you posted your formula. In the meantime, here are a few general comments:

INDIRECT.EXT does not work with
• PC’s running Windows Me (Millennium version)
• defined names in closed workbooks.
• Range arguments (it can only return single values)

References:
http://makeashorterlink.com/?O2E512068
http://makeashorterlink.com/?H21621068

In addition, you cannot copy a formula containing INDIRECT.EXT by dragging the formula down/across a range, unless you make a slight adjustment to the syntax e.g. you wish to get data from say cells A1, A2 and A3 from a closed file named Test1.xls, located at C:\Temp3:

B4:
=INDIRECT.EXT("'C:\Temp3\[Test1.xls]Sheet3'!A"&C4)
drag the formula down.

C4 contains 1, C5 contains 2 and C6 contains 3.

Some alternatives to INDIRECT.EXT at http://makeashorterlink.com/?T1D723297 .
The reference also includes an example on how to use INDIRECT.EXT in conjunction with the Sumproduct function (not tested).

Other alternatives here:http://www.mrexcel.com/board2/viewtopic.php?t=67212
Nimrod’s macro works very well.

Other notes:

If you just want to link to a closed workbook/worksheet, you can simply use:

='C:\Temp3\[Test1.xls]sheet1'!A1

If you are doing lookups, Vlookup/HLookup and Index/Match work with closed workbooks (you do not have to use INDIRECT.EXT).

Harlan Grove’s “Pull” function works very well (I use this in a few applications): see Jon's post above.

Warning:
Do not try to drag Mr. Grove’s UDF formula down or across the page. On my PC, Excel “freezes” and requires Ctrl-Alt-Delete to get out of it. Similarly, in one application I have a validation dropdown that houses file names of closed workbooks. If I selected a file name that does not exist, the UDF went into an endless loop. In this case, only a hard re-boot worked (Ctrl-Alt-Delete failed).

Regards,


Mike
Click to expand...


Thanks for responding.
First of all I AM running Windows ME. Maybe that's my problem.

My formula is as follows:
=Indirect.ext("'c:\my documents\my excel\[" & A2 & " Inv.xls]entry sheet'!C6")
A2 contains a store number ie: 01

Thanks again!
 
Upvote 0
John,

Using Excel XP

Is your file named “01 Inv.xls” (note the space) or “01Inv.xls”?

Your formula works for me when the space before “Inv” is eliminated. For testing purposes, my path is “C:\Temp3\. The file is closed.

=INDIRECT.EXT("'C:\Temp3\["&A2&"Inv.xls]entry sheet'!C6")

In your formula, there is a space in front of “Inv”

=Indirect.ext("'c:\my documents\my excel\[" & A2 & " Inv.xls]entry sheet'!C6")

Accordingly, the formula resolves to “01 Inv.xls” not “01Inv.xls”.

If the space is not the problem, then it appears that your version of Excel will not work with INDIRECT.EXT.

I assume that you have thoroughly checked that your path description is correct, that a worksheet named “entry sheet” exists in 01Inv.xls etc. Note that “ entry sheet” and “entry sheet ” (note the leading and trailing space) is not the same as “entry sheet” (no spaces).

Have you tried Harlan Grove’s “Pull” function?

Regards,

Mike
 
Upvote 0
Ekim said:
John,

Using Excel XP

Is your file named “01 Inv.xls” (note the space) or “01Inv.xls”?

Your formula works for me when the space before “Inv” is eliminated. For testing purposes, my path is “C:\Temp3\. The file is closed.

=INDIRECT.EXT("'C:\Temp3\["&A2&"Inv.xls]entry sheet'!C6")

In your formula, there is a space in front of “Inv”

=Indirect.ext("'c:\my documents\my excel\[" & A2 & " Inv.xls]entry sheet'!C6")

The workbook name is "01 Inv.xls". There is a space between 01 and Inv.
I'm using Excel 97.
I upgraded to Windows XP Home Edition today and now just plain INDIRECT
doesn't work when the workbook is open and it did under Windows ME.
INDIRECT.EXT still doesn't work
I feel like I'm going backwards. I really don't like XP and would like to go back to ME but I guess I'll stick with it.
I'm thinking of trying ACCESS and writing my application from scratch and forget about EXCEL.
Any advise???
Accordingly, the formula resolves to “01 Inv.xls” not “01Inv.xls”.

If the space is not the problem, then it appears that your version of Excel will not work with INDIRECT.EXT.

I assume that you have thoroughly checked that your path description is correct, that a worksheet named “entry sheet” exists in 01Inv.xls etc. Note that “ entry sheet” and “entry sheet ” (note the leading and trailing space) is not the same as “entry sheet” (no spaces).

Have you tried Harlan Grove’s “Pull” function?

Regards,

Mike
Click to expand...
 
Upvote 0
Quite interesting the Harlan Grove’s “Pull” function.
However I already used the indirect.ext in too many files to change it all.

I am working on a project where we will receive hundreds of questionnaires (some consist of 850 questions...). With some 25 files I pull data (the answers) out of these questionnaires. With that amount of data I needed a very flexible cell reference: filename (name of the respondent + xls), sheetname and even cell column and row.

Uptill now I use:
=IF(ISERROR(SETV(INDIRECT.EXT("'"&Location!$A$1&"["&$C3&".xls]"&$C$4&"'!$"&$F$3&$G3)));"";IF(GETV() <> 0;GETV();""))

Where ISERROR takes care of #REF! (when some questionnaires are not in yet) and GETV() <> 0 takes care of an annoying zero. SETV/GETV makes the formula faster.

I works rather fine, but still I have some problems with the speed and more annoying I sometimes experience an update failure. Changing ISERROR to ISERR or visa versa helps, but that is not a solution. Any suggestions?
 
Upvote 0
Ekim said:
John,

Using Excel XP

Is your file named “01 Inv.xls” (note the space) or “01Inv.xls”?

Your formula works for me when the space before “Inv” is eliminated. For testing purposes, my path is “C:\Temp3\. The file is closed.

=INDIRECT.EXT("'C:\Temp3\["&A2&"Inv.xls]entry sheet'!C6")

In your formula, there is a space in front of “Inv”

=Indirect.ext("'c:\my documents\my excel\[" & A2 & " Inv.xls]entry sheet'!C6")

Accordingly, the formula resolves to “01 Inv.xls” not “01Inv.xls”.

If the space is not the problem, then it appears that your version of Excel will not work with INDIRECT.EXT.

I assume that you have thoroughly checked that your path description is correct, that a worksheet named “entry sheet” exists in 01Inv.xls etc. Note that “ entry sheet” and “entry sheet ” (note the leading and trailing space) is not the same as “entry sheet” (no spaces).

Have you tried Harlan Grove’s “Pull” function?

Regards,

Mike
Click to expand...

:biggrin:
Sorry I screwed up my last responce by typing in the middle of your responce.
I finally figured out why INDIRECT.EXT wouldn't work for me.
When I upgraded to XP it added 2 new folders to my path to the workbooks. No wonder it couldn't find my workbooks! I corrected the path
and INDIRECT.EXT works like a champ. I still hate XP and am experiencing
many problems.
Thanks again
 
Upvote 0
I figured out why the ISERR and ISERROR in the formula was unstable. It had to do with our company network. I don't know what the exact reason is. In any case, when I copied the questionnaires and the files with the indirect.ext formulas to pull data out of the questionnaires to my hard drive, the add in worked flawlessly.
So test your Excel file in a network environment if that's necessary.

Good luck!
 
Upvote 0
255 character limitation of indirect.ext and grove's "p

Help, this is a disaster... I discovered that indirect.ext (add in of morefunc) only pulls the first 255 characters of the contents of a cell, when the target file is closed. It does pull out the full 1024 characters when both files are open.
This is not what indirect.ext was designed for.

I also tried Grove's pull-function, but unfortunately this also has the 255 characters limitation.
Only when using the direct
=c:\test.xls[sheet1]!A1
results in a full loaded cell of the max. visible 1024 characters.

I can't open 172 (!) files together to pull data into my file... you would need 2 Gigs of RAM.....
Anybody has a solution? I discovered this after 2 months of work on hundreds of sheets that will pull data out of another hundreds of files (questionnaires). Now I really freaking out!!!!!!!

I use formulas like
=IF(ISERROR(SETV(INDIRECT.EXT("'"&Locaties!$A$1&"["&A11&".xls]"&$C$3&"'!$B$"&$D$3)));"";IF(GETV() <> 0;GETV();""))
and
=IF(ISERROR(INDIRECT.EXT("'"&Locaties!$A$1&"["&A10&".xls]"&$C$3&"'!$B$"&$D$3));"";INDIRECT.EXT("'"&Locaties!$A$1&"["&A10&".xls]"&$C$3&"'!$B$"&$D$3))
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Show a form created with an addin in active window with 2 or more different sheets
Replies
4
Views
322
BennyBatt
B
N
  • Solved
Use different sheet names as part of a formula (indirect function?)
Replies
3
Views
302
awoohaw
awoohaw
C
  • Solved
Sum referenced cells in Sheet range using SUM(INDIRECT()) #REF! ERROR
2
Replies
12
Views
202
Fluff
Fluff
T
  • Question
Spreadsheet Crashing
Replies
3
Views
151
Michael M
Michael M
M
  • Question
Seeking alternate/better solution for excel XLAM addin, currently using form/multipages
Replies
1
Views
431
Exceljeanie
E

Forum statistics

Threads
1,214,918
Messages
6,122,243
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top