I have an excel spreadsheet which uses INDIRECT.EXT to read data from about
10 excel files using morefunc 3.241. This worked brilliantly and have never
had any problems.

Recently, I installed the latest version and everything fell apart. The data
is not being read at all.

My codes are as follows:

=IF('Sales'!$J10=0,INDIRECT.EXT('Sales'!$U10& $C10 &".xls]YTD'!$M$6"),"")

where, $U10 value is exactly as follows:

'C:\Expenses and Sales\Equipment Sales\Sales\WA_Region\Sales\2007\Sales2007_

and, $C10 value is exactly as follows:

smithdg

I am afraid, there were reasons why I had to use the above to locate and
name the file.

Can anyone please help, I need this. Many thanks in advance.

SU wrote...
I have an excel spreadsheet which uses INDIRECT.EXT to read data from about
10 excel files using morefunc 3.241. This worked brilliantly and have never
had any problems.

Recently, I installed the latest version and everything fell apart. The data
is not being read at all.
....

First, can you revert to the older version? That would be the most
expedient thing to do.

FWIW, INDIRECT.EXT works on most of the machines I've tried it on, but
it never worked on my wife's Windows ME machine (which we upgraded to
Windows XP Pro, after which it did work). I believe others have
reported INDIRECT.EXT not working with other Windows versions.

where, $U10 value is exactly as follows:

'C:\Expenses and Sales\Equipment Sales\Sales\WA_Region\Sales\2007\Sales2007_

With the trailing underscore?

and, $C10 value is exactly as follows:

smithdg

As written, your argument to INDIRECT.EXT,

'Sales'!$U10 & $C10 & ".xls]YTD'!$M$6"

would evaluate to

'C:\Expenses and Sales\Equipment Sales\Sales\WA_Region\Sales
\2007\Sales2007_smithdg.xls]YTD'!$M$6

and that's not a valid external reference.

The first step in debugging INDIRECT.EXT is to put its argument as a
stand-alone formula into another cell (say X99), and evaluate it as a
text string. Does it look right? Then copy that cell and paste it as a
value into another cell (Y99) and press

[F2] [Home] = [Enter]

in succession to enter it as an constant/literal external reference.
Does that evaluate as expected? If not, then INDIRECT.EXT *should*
also fail. However, if that formula works, but

=INDIRECT.EXT(X99)

fails, then INDIRECT.EXT in the latest version of MOREFUNC.XLL just
won't work with your PC. It happens, and it seems to be unpredictable.

Harlan
Thank you for your suggestions. I am running XP Pro. Unfortuneately, I have
overwritten the v3.241 with the latest version and am desperately trying to
locate the old version in my computer.

U10 is indeed with the trailing underscore.

I'll work with your suggestions and get back to you with the results.

Once again, Thank you.


"Harlan Grove" wrote:

SU wrote...
I have an excel spreadsheet which uses INDIRECT.EXT to read data from about
10 excel files using morefunc 3.241. This worked brilliantly and have never
had any problems.

Recently, I installed the latest version and everything fell apart. The data
is not being read at all.
....

First, can you revert to the older version? That would be the most
expedient thing to do.

FWIW, INDIRECT.EXT works on most of the machines I've tried it on, but
it never worked on my wife's Windows ME machine (which we upgraded to
Windows XP Pro, after which it did work). I believe others have
reported INDIRECT.EXT not working with other Windows versions.

where, $U10 value is exactly as follows:

'C:\Expenses and Sales\Equipment Sales\Sales\WA_Region\Sales\2007\Sales2007_

With the trailing underscore?

and, $C10 value is exactly as follows:

smithdg

As written, your argument to INDIRECT.EXT,

'Sales'!$U10 & $C10 & ".xls]YTD'!$M$6"

would evaluate to

'C:\Expenses and Sales\Equipment Sales\Sales\WA_Region\Sales
\2007\Sales2007_smithdg.xls]YTD'!$M$6

and that's not a valid external reference.

The first step in debugging INDIRECT.EXT is to put its argument as a
stand-alone formula into another cell (say X99), and evaluate it as a
text string. Does it look right? Then copy that cell and paste it as a
value into another cell (Y99) and press

[F2] [Home] = [Enter]

in succession to enter it as an constant/literal external reference.
Does that evaluate as expected? If not, then INDIRECT.EXT *should*
also fail. However, if that formula works, but

=INDIRECT.EXT(X99)

fails, then INDIRECT.EXT in the latest version of MOREFUNC.XLL just
won't work with your PC. It happens, and it seems to be unpredictable.