Help with Indirect.ext

cav~firez22

Well-known Member
Joined
Jun 21, 2006
Messages
543
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?
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Joined
Jul 30, 2006
Messages
3,656
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

Well-known Member
Joined
Jun 21, 2006
Messages
543
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

Well-known Member
Joined
Jun 21, 2006
Messages
543

ADVERTISEMENT

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
 
Joined
Jul 30, 2006
Messages
3,656
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

Well-known Member
Joined
Jun 21, 2006
Messages
543
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?
 
Joined
Jul 30, 2006
Messages
3,656
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
 

Forum statistics

Threads
1,136,655
Messages
5,677,017
Members
419,668
Latest member
DharmaK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top