morefunc indirect.ext

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
i'm trying to take advantage of the indirect.ext function in the morefunc.xla add-in. it's a great tool. my question is this: when the file my function is referencing closes, i only get one good set of numbers. upon a recalculation, all computations return with the #REF! error like the book does not exist. i think my syntax is right. what am i doing wrong?

my formula looks like this (Q5 in example):

=INDIRECT.EXT("[WTR04.xls]"&P5&"!$BN$34")

if i use the full path as suggested by the formula, my formula looks like this (Q4 in the example):

=INDIRECT.EXT("C:\Documents and Settings\All Users\Documents\[WTR04.xls]Mar04!BK35")

the latter equation will always return the #REF! error. i've read mr longre's help files, but can't seem to nail down what i'm doing wrong here. an example looks like this:
TKN Loading Send 04-05.xls
PQRS
3dategal.tkn.lbs.
4Mar04#REF!63.00#REF!
5Apr04#REF!52.20#REF!
Farm1


as it stands i'm utilizing the first of the two equations and trying not to recalculuate when using this workbook, but that is growing cumbersome as it's evolved to a 'daily-entry' type workbook.

if anyone has any ideas/suggestions, i'd love to hear them. thanks!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
I have never used this before but after looking at the help file, your syntax looks like it's missing the single quotes:

This is the example from the help file:
=INDIRECT.EXT("'C:\My Documents\[Workbook5.xls]Sheet1'!A9")

Based on your 2nd sample and the help file, maybe:
=INDIRECT.EXT("'C:\Documents and Settings\All Users\Documents\[WTR04.xls]Mar04'!BK35")
 
Upvote 0
HOTPEPPER!!!

that was it! oh my, it is the little things isn't it! i really missed those didn't i ! thank you!
 
Upvote 0
I Know this is an old thread but some problems never die.
Excel 2013 and still indirect wont work with closed books.

I am needing to reference a closed book and have previously used the following:
=IF(E38="","",VLOOKUP(E38,'S:\PB\[2014-04-01-InternalCatalog.xlsx]Catalog'!$A$2:$H$1000,2,FALSE))
Its clunky but it works.

Then I thought I would make it a bit better as updates were painful in having to change many formulas on a page

I modified the formula to:
=IF(E37="","",VLOOKUP(E37,N6&"!$A$2:$H$1000",2,FALSE))
Where N6 = 2014-04-01-InternalCatalog.xlsx]Catalog
this gave me #Value! error

I then again modified the formula to:
=IF(E35="","",VLOOKUP(E35,INDIRECT.EXT(N6&"!$A$2:$H$1000"),2,FALSE))
Using the Indirect function was not an option as it doesn't work on closed books so I installed the indirect.ext add in.
This gave me the #Ref! error

Thinking it may be the cell value causing the error, I substituted the cell for the actual value and tried this:
=IF(E36="","",VLOOKUP(E36,INDIRECT.EXT("'S:\PB\[2014-04-01-InternalCatalog.xlsx]Catalog'!$A$2:$H$1000"),2,FALSE))
This gave me the #N/A error

I am stumped as to what the error is or how to resolve it

All I wish to do is have the filename as a value in a cell so that when the book is updated, I only have to change one cell value not 50

Any and all suggestions will be most welcome.

Thanks,
Inta.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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