INDIRECT.EXT question

[SU]

I am trying to use INDIRECT.EXT function to get values from a different
worksheet.

I am typing the path in a cell in the following format in cell A1:

"'G:\Operations\Timesheets\2005\[2005_abcd.xls]YTD'!$N$6"

and then using

INDIRECT.EXT(A1) function

but I am getting #REF instead of any values.

Can anybody help resolve this or suggest a better option. I have to type
the path there is no way around it. Please help.

 

[Harlan Grove]

SU wrote...

I am trying to use INDIRECT.EXT function to get values from a different
worksheet.

I am typing the path in a cell in the following format in cell A1:

"'G:\Operations\Timesheets\2005\[2005_abcd.xls]YTD'!$N$6"

and then using

INDIRECT.EXT(A1) function

but I am getting #REF instead of any values.

....

Any reason you're not simply using

='G:\Operations\Timesheets\2005\[2005_abcd.xls]YTD'!$N$6

?

If cell A1 is the result of a formula, then there are simply some
configurations of Windows and Excel/Office under which INDIRECT.EXT
doesn't work. Laurent Longre and I corresponded about this two years
ago, but there was no resolution. FWIW, it didn't work on my wife's Pc
running Windows Me and Excel 2000, but it works on the other PCs I use.

Alternatives:

http://groups-beta.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075

(or http://makeashorterlink.com/?F2993260A ).

 

[Laurent Longre]

Hello Harlan,

Harlan Grove a écrit :

If cell A1 is the result of a formula, then there are simply some
configurations of Windows and Excel/Office under which INDIRECT.EXT
doesn't work. Laurent Longre and I corresponded about this two years
ago, but there was no resolution.

I have recently changed the code of this function, it could work better in these
configurations (I can't be sure, because I have only XP). Otherwise I'm working
on another function named INDIRECT2, but it is only a pre-beta version. It
parses directly the binary Excel file format. If you want to take a look:
http://xcell05.free.fr/forums/viewtopic.php?id=27.

Your "PULL" function is an excellent alternative, nice work.

(BTW my site has moved to http://xcell05.free.fr)

Cordially,

Laurent

 

[SU]

Harlan & Laurent
I can't use ='G:\Operations\Timesheets\2005\[2005_abcd.xls]YTD'!$N$6
directly since I am trying to achieve automation so when new employees join
in their timesheet will automatically updated just by adding a name.

I suspect my answer may not make any sense to you (it doesn't to me!) but I
am looking for an alternative rather than typing the link.

Any suggestion will be appreciated. I'll try any alternative that you may
offer. Many many thanks.

SU wrote...

I am trying to use INDIRECT.EXT function to get values from a different
worksheet.

I am typing the path in a cell in the following format in cell A1:

"'G:\Operations\Timesheets\2005\[2005_abcd.xls]YTD'!$N$6"

and then using

INDIRECT.EXT(A1) function

but I am getting #REF instead of any values.

....

Any reason you're not simply using

='G:\Operations\Timesheets\2005\[2005_abcd.xls]YTD'!$N$6

?

If cell A1 is the result of a formula, then there are simply some
configurations of Windows and Excel/Office under which INDIRECT.EXT
doesn't work. Laurent Longre and I corresponded about this two years
ago, but there was no resolution. FWIW, it didn't work on my wife's Pc
running Windows Me and Excel 2000, but it works on the other PCs I use.

Alternatives:

http://groups-beta.google.com/group/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075

(or http://makeashorterlink.com/?F2993260A ).

 

[macropod]

Hi SU,

Firstly, the INDIRECT function doesn't have an '.EXT' extension.
Secondly, when used with external workbooks, the INDIRECT function only
works if the source workbook is open. If the source workbook is not open,
the INDIRECT function returns #REF!

Cheers

 

[Gord Dibben]

macropod

INDIRECT.EXT is a UDF by Laurent Longre.

Works with closed workbooks.

Can be downloaded as one of the Functions available in MOREFUNC.XLL from
Laurent's site.

http://xcell05.free.fr/


Gord Dibben Excel MVP

Hi SU,

Firstly, the INDIRECT function doesn't have an '.EXT' extension.
Secondly, when used with external workbooks, the INDIRECT function only
works if the source workbook is open. If the source workbook is not open,
the INDIRECT function returns #REF!

Cheers

I am trying to use INDIRECT.EXT function to get values from a different
worksheet.

I am typing the path in a cell in the following format in cell A1:

"'G:\Operations\Timesheets\2005\[2005_abcd.xls]YTD'!$N$6"

and then using

INDIRECT.EXT(A1) function

but I am getting #REF instead of any values.

Can anybody help resolve this or suggest a better option. I have to type
the path there is no way around it. Please help.

 

[macropod]

Thanks for that. In that case, perhaps the OP should contact Laurent's site.

Cheers

macropod

INDIRECT.EXT is a UDF by Laurent Longre.

Works with closed workbooks.

Can be downloaded as one of the Functions available in MOREFUNC.XLL from
Laurent's site.

http://xcell05.free.fr/


Gord Dibben Excel MVP

Hi SU,

Firstly, the INDIRECT function doesn't have an '.EXT' extension.
Secondly, when used with external workbooks, the INDIRECT function only
works if the source workbook is open. If the source workbook is not open,
the INDIRECT function returns #REF!

Cheers

I am trying to use INDIRECT.EXT function to get values from a different
worksheet.

I am typing the path in a cell in the following format in cell A1:

"'G:\Operations\Timesheets\2005\[2005_abcd.xls]YTD'!$N$6"

and then using

INDIRECT.EXT(A1) function

but I am getting #REF instead of any values.

Can anybody help resolve this or suggest a better option. I have to type
the path there is no way around it. Please help.

 

[Harlan Grove]

macropod wrote...

Thanks for that. In that case, perhaps the OP should contact Laurent's

site.
....

Or perhaps you should pull your head out of your ... and realize that
questions about functions provided in MOREFUNC.XLL are answered all the
time in the microsoft.public.excel.* newsgroups.

A basic rule for newsgroup participation: if you don't understand a
question, don't try to answer it. If you make an ill-considered attempt
to answer it, don't get snotty when others point out just how ignorant
you are.

 

[Harlan Grove]

Laurent Longre wrote...
....

I have recently changed the code of this function, it could work better in these
configurations (I can't be sure, because I have only XP). Otherwise I'm working
on another function named INDIRECT2, but it is only a pre-beta version. It
parses directly the binary Excel file format. If you want to take a look:
http://xcell05.free.fr/forums/viewtopic.php?id=27.

....

Parsing binary files is suboptimal. Systems on which INDIRECT.EXT and
pull work can extract values from the supported 123 .WK? file formats
in addition to all .XLS file formats. Which versions of the .XLS file
format would you support? How about XL4 .XLW file format? The 123 .WK?
file formats?

INDIRECT2 reading closed files directly may be faster, but if speed is
the main requirement, using formulas like

="='"&Pathname&"\["&Filename&"]"&Sheetname&"'!"&RangeAddress

to generate string representations of external reference links, copying
them and pasting special as values, then using Edit > Replace to
replace = with = in the pasted range would convert text constants to
formulas, and those explicit external reference links would calculate
faster than any add-in function.

Just my opinion, but flexibility is the paramount need in this type of
function. Your INDIRECT2 would seem to reduce flexibility compared to
INDIRECT.EXT.