Help with Indirect.ext

[cav~firez22]

I am currently having on hell of a time with the following equation

=INDIRECT("'[Collections KPI.xls]"&L1&"'!$E$10")+INDIRECT("'[Collections KPI.xls]"&L1&"'!$H$10")

I have not been able to get INDIRECT.EXT to work correctly

I need the formula, that is in workbook (Collections.xls) to pull info from cells that are in Collections KPI.xls with out haveing to open the kpi sheet.

any ideas with this?

 

[stanleydgromjr]

Searching in Excel Help:
INDIRECT(ref_text,a1)

Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.


Are Collestions.xls and Collections KPI.xls in the same folder?

What is the full path where Collections KPI.xls resides?

What is the sheetname in Collections KPI.xls?

What is the address of the cell or cells, in Collections KPI.xls, that you are trying to pull into Collections.xls?


Have a great day,
Stan

 

[acw]

Hi

A quick google on the indirect.ext from morefunc addin shows that the full pathname is required for the file.

HTH

Tony

 

[cav~firez22]

Searching in Excel Help:
INDIRECT(ref_text,a1)

Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.


Are Collestions.xls and Collections KPI.xls in the same folder?

What is the full path where Collections KPI.xls resides?

What is the sheetname in Collections KPI.xls?

What is the address of the cell or cells, in Collections KPI.xls, that you are trying to pull into Collections.xls?


Have a great day,
Stan

=INDIRECT.EXT("'O:\Shared Services\Continuous Improvement\Manual Data\KPI's\[Collections KPI.xls]"&L1&"'!$E$10")+INDIRECT.EXT("'O:\Shared Services\Continuous Improvement\Manual Data\KPI's\[Collections KPI.xls]"&L1&"'!$H$10")

Thats what im using and i get #Value

Files are not in same folder.

Same Drive though,

Trying to get the Sum of E10+H10

 

[cav~firez22]

Searching in Excel Help:
INDIRECT(ref_text,a1)

Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.


Are Collestions.xls and Collections KPI.xls in the same folder?

What is the full path where Collections KPI.xls resides?

What is the sheetname in Collections KPI.xls?

What is the address of the cell or cells, in Collections KPI.xls, that you are trying to pull into Collections.xls?


Have a great day,
Stan

=INDIRECT.EXT("'O:\Shared Services\Continuous Improvement\Manual Data\KPI's\[Collections KPI.xls]"&L1&"'!$E$10")+INDIRECT.EXT("'O:\Shared Services\Continuous Improvement\Manual Data\KPI's\[Collections KPI.xls]"&L1&"'!$H$10")

Thats what im using and i get #Value

Files are not in same folder.

Same Drive though,

Trying to get the Sum of E10+H10

 

[stanleydgromjr]

Suming two cells in a closed workbook

If, and only if, the sheetname is L1, then try this formula:

='O:\Shared Services\Continuous Improvement\Manual Data\KPI's\[Collections KPI.xls]L1'!$E$10+'O:\Shared Services\Continuous Improvement\Manual Data\KPI's\[Collections KPI.xls]L1'!$H$10


Have a great day,
Stan

 

[cav~firez22]

Thanks for the help.. I fixed it. apparently it was the folder name KPI's. I guess it didnt like the ' so i renamed folder to KPIs and all the values poped up.

However, it is VERY SLOW when calculating the cells, any way to speed that up?

 

[stanleydgromjr]

Did you use your formula or mine?

If you did not use my formula, please try it.

If you used my formula, it should have added the two cells extremely fast.


Have a great day,
Stan