Help! Problem using Indirect function to update master workbook from linked files

Indirect will not work on closed sheets. It's a little "feature" that is a bit irritating. If you can use 3rd party add-ins, there's a useful (free) thing caled "Morefunc", which includes INDIRECT.EXT, which is just like INDIRECT, except that it works on closed sheets, as well. Google either of these if you can use them.

Hi,

Welcome to the Forum.

Looks like you would need a VBA macro code for your requirement. Try modifying the code in the below link to suit your needs.
Import data from a closed workbook (ADO)

Again thanks for the replies. I also tried the macro option suggested on this thread. I copied and pasted the code in the second block:

Please Login or Register  to view this content.

What I got was a compile error, saying Compile error, user defined type not defined
And this was pointing to the line: "Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset"
Kindly excuse if this is something basic, as I am a newbie with macros.

@ treetrunk...Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I have added them for you - this time

Odd.

Here is an example of a working script (minus the "=" sign).

INDIRECT.EXT("'C:\Users\1003011\Documents\Depot\03 December 2013\02 XLSX\["&$A4&".xlsx]Summary'!c17")

It is picking up a name (MYNAME) from cell a4 of the open document, and returing cell C17 from the sheet called Summary, from MYNAME.xlsx, filed at the location shown.

@Glenn, thanks, But when I tried this:
=INDIRECT.EXT("'C:\Users\Administrator\Desktop\FS\7\Sta\["&$A5&".xlsx]Testcases'!L8"); which is intended to get the value (say "feature1") of cell A5 from the current workbook, and getting the value of the feature1.xlsx book, sheet: Testcases, cell L8, I get #NAME? in the result cell.
It will be great if this can somehow work...what may now be wrong?

It may be that INDIRECT.EXT is not properly recognized, cause when I evaluate the formula, it shows #NAME?(.content of brackets as above....)
Are any settings required to make the MoreFunc INDIRECT.EXT work?

To include macros in your function, after opening your workbook, you use the shortcut key Alt + F11 to open the Excel VBA Editor.

After the VBA Editor window is opened, you need to place the Sub function in your post #5 inside a module. Also you need to add References from the tools menu if any required. And you need to place a button on your worksheet to call the sub or you can use the shortcut key Alt + F8 to invoke the function.

Sorry if this is a silly question - but have you installed it as an add-in? If you go to formulas/insert function/category do you see morefunc at the bottom of the drop down list of categories?

Wooo Hooo!! Getting towards a result...

One followup question w.r.t INDIRECT.EXT:
=INDIRECT.EXT("'<Path need value from cell C3 here>\["&$A5&".xlsx]Testcases'!L8")
How can this be done?
I tried:
=INDIRECT.EXT("'"&$C3&"\["&$A5&".xlsx]Testcases'!L8") - didnt work
=INDIRECT.EXT("'"$C3"\["&$A5&".xlsx]Testcases'!L8") - didnt work
etc..

It will be required to get the values for the path from two cells
so the path will be
<main path in cell C3>\<subfolder name in cell B2, B3....etc as per row no>

This is the last link...if this can be fixed it will be wonderful.

What EXACTLY is in C3?

You might need to add the sheetname as well as the cell number. I'll have a look at some other places where ive used INDIRECT.EXT

You need more than 1 INDIRECT function to make it wiork. It's too late to try to work out the syntax for today....

No you don't. See below.

OK, here we go again. I have attached two files to let you see how it worked. Put them in the same place and amend row A entries to reflect your situation & BINGO... it works (or at least it waas working when it left here!!).

One critical point. There is a single tick at the start of each entry in column A. You can see it in the formula bar, but not in the actual cell. You need it.

I find INDIRECT.EXT to be totally frustrating & totally invaluable at one and the same time. It IS worth working at!!

Thanks. If that's everything working as it should, can you mark the thread as solved?

In your thread, below the thread title on the right side, you can find a menu titled Thread Tools. Click that and choose "Mark this thread as SOLVED" to mark it solved

Sorry, I haven't seen this problem before and, like you, I'm using Excel 2007.