Indirect to a closed spreadsheet

[MarkS]

Hi,
I have a formula
=INDIRECT("'S:\STAR\Renewable Energy\Renewable Position Reports\Positions
Re-Pointed to ECO\[CCX Position Report - 140408.xls]AGL position - data'!B" &
COUNT('S:\STAR\Renewable Energy\Renewable Position Reports\Positions
Re-Pointed to ECO\[CCX Position Report - 140408.xls]AGL position -
data'!J63:J200) + 62 )

Which finds the latest contract, which are a mix of text and numeric, the
count part works fine BUT the indirect part fails

Has anyone made a add-in that deals withthis problem, or is there a way
around this problem.

I have no Control over the remote spread sheet

Thanka MarkS

 

[Barb Reinhardt]

If you want to use INDIRECT on a closed workbook, you'll need to use
Indirect.ext which is in the MOREFUNC add in found here

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

I believe that it opens the workbook and extracts the data every time the
worksheet is calculated. If I'm wrong, someone can correct me.

HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

 

[JMB]

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

 

[MarkS]

Hi,
I'll download that and try it out, there is a note that it sometimes doesn't
work

Thanks MarkS

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

Hi,
I have a formula
=INDIRECT("'S:\STAR\Renewable Energy\Renewable Position Reports\Positions
Re-Pointed to ECO\[CCX Position Report - 140408.xls]AGL position - data'!B" &
COUNT('S:\STAR\Renewable Energy\Renewable Position Reports\Positions
Re-Pointed to ECO\[CCX Position Report - 140408.xls]AGL position -
data'!J63:J200) + 62 )

Which finds the latest contract, which are a mix of text and numeric, the
count part works fine BUT the indirect part fails

Has anyone made a add-in that deals withthis problem, or is there a way
around this problem.

I have no Control over the remote spread sheet

Thanka MarkS

 

[MarkS]

Hi,

Installed morefunc, works great. The stats functions look useful, will have
to give them a try

Thanks

Hi,
I'll download that and try it out, there is a note that it sometimes doesn't
work

Thanks MarkS

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

Hi,
I have a formula
=INDIRECT("'S:\STAR\Renewable Energy\Renewable Position Reports\Positions
Re-Pointed to ECO\[CCX Position Report - 140408.xls]AGL position - data'!B" &
COUNT('S:\STAR\Renewable Energy\Renewable Position Reports\Positions
Re-Pointed to ECO\[CCX Position Report - 140408.xls]AGL position -
data'!J63:J200) + 62 )

Which finds the latest contract, which are a mix of text and numeric, the
count part works fine BUT the indirect part fails

Has anyone made a add-in that deals withthis problem, or is there a way
around this problem.

I have no Control over the remote spread sheet

Thanka MarkS

 

[Barb Reinhardt]

It appears to me that the Indirect.Ext function opens the workbook and
updates the cell every time the worksheet is calculated. If you have a lot
of them and the closed workbooks are on a server elsewhere, you may want to
rethink your approach.

FWIW, I used this until the calculation time was extreme and then learned
more about VBA to do what I wanted to do.

Barb Reinhardt

Hi,

Installed morefunc, works great. The stats functions look useful, will have
to give them a try

Thanks

Hi,
I'll download that and try it out, there is a note that it sometimes doesn't
work

Thanks MarkS

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

:

Hi,
I have a formula
=INDIRECT("'S:\STAR\Renewable Energy\Renewable Position Reports\Positions
Re-Pointed to ECO\[CCX Position Report - 140408.xls]AGL position - data'!B" &
COUNT('S:\STAR\Renewable Energy\Renewable Position Reports\Positions
Re-Pointed to ECO\[CCX Position Report - 140408.xls]AGL position -
data'!J63:J200) + 62 )

Which finds the latest contract, which are a mix of text and numeric, the
count part works fine BUT the indirect part fails

Has anyone made a add-in that deals withthis problem, or is there a way
around this problem.

I have no Control over the remote spread sheet

Thanka MarkS