Using Indirect to link dynamically with another work book

[Journeyman3000]

Hi all,

I am try to link to another workbook. Easy enough if the book is known.

However, I need the user to select the location of the book, which then updates the sheet.

Assume H2 is the location and name of the workbook (e.g. C:\Directory\Workbook.xls), I tried:

Please Login or Register  to view this content.

This isn't working and i'm probably missing something minor.

Can someone suggest the correct formula plz

Cheers

[ajryan88]

Hi,

Try

Please Login or Register  to view this content.

The trick here is that the format should look like this

Please Login or Register  to view this content.

So you have to separate the directory and the workbook name from one another, manually insert the apostrophes and brackets, and then concatenate the string back to the correct format.

Note that this is an array formula, and requires Ctrl + Shift + Enter as opposed to just Enter, otherwise you will receive an error in the cell.

I hope this helps

[FDibbins]

INDIRECT() only works on open workbooks, not with closed workbooks. If your WB will be closed, then try using the Morefunc add-in (google for the download)

[Journeyman3000]

Okay, thanks AJRyan88, and FDibbins. Take a rep for your responses!

The formula provided will be enough to do what I need.

Also i'm aware that indirect is useable only on open books, but both books are almost always opened together.

Cheers

[FDibbins]

Happy to help and thanks for the feedback

[ajryan88]

You're welcome!

Just as a note on your last statement...you could alter the formula to see if the other workbook is open:

Please Login or Register  to view this content.

Please don't forget to mark this thread as solved

Have a great day