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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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
 
Upvote 0
Hi

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

HTH

Tony
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top