Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect to a closed spreadsheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect to a closed spreadsheet
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. "MarkS" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect to a closed spreadsheet
http://xcell05.free.fr/morefunc/english/index.htm "MarkS" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect to a closed spreadsheet
Hi,
I'll download that and try it out, there is a note that it sometimes doesn't work Thanks MarkS "JMB" wrote: http://xcell05.free.fr/morefunc/english/index.htm "MarkS" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect to a closed spreadsheet
Hi,
Installed morefunc, works great. The stats functions look useful, will have to give them a try Thanks "MarkS" wrote: Hi, I'll download that and try it out, there is a note that it sometimes doesn't work Thanks MarkS "JMB" wrote: http://xcell05.free.fr/morefunc/english/index.htm "MarkS" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Indirect to a closed spreadsheet
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 "MarkS" wrote: Hi, Installed morefunc, works great. The stats functions look useful, will have to give them a try Thanks "MarkS" wrote: Hi, I'll download that and try it out, there is a note that it sometimes doesn't work Thanks MarkS "JMB" wrote: http://xcell05.free.fr/morefunc/english/index.htm "MarkS" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use of the Indirect function on a closed workbook. | Excel Discussion (Misc queries) | |||
Indirect referencing closed spreadsheets | Excel Discussion (Misc queries) | |||
INDIRECT should be updated to work on closed workbooks. | Excel Worksheet Functions | |||
what exactly prevents INDIRECT from accessing closed worksheets? | Excel Worksheet Functions | |||
Indirect to a closed workbook | Excel Programming |