#REF! error tyring to get data from another wb using Indirect formula

[kit99]

In my wb1/sheet1, I got this cells:
A21 = Full name of wb I'm trying to get data from ("Test1.xlsx")
B9 = Name of sheet in wb Test1.xlsx ("Eget materiell")
B10 = Name of cell reference wb Test1.xlsx ("B2")

If I put in this formula in a cell in wb1/sheet1:

='[Test1.xlsx]Eget materiell'!$B$2

everyting works.

But if I try to do this:

=INDIRECT("'["&A21&"]"&B$9&"'!"&B$10)

i get a #REF ! error.

How can I the "indirect" formula to work?
All workbooks are in the same folder.

 

[steve the fish]

Aside from not using the inverted commas in the cells that will work. Check any spelling mistakes.

 

[kit99]

Aside from not using the inverted commas in the cells that will work. Check any spelling mistakes.

Thanks for suggestion. And by doing so, I just found out that all formulas work as they should - given that I open up wb "Test1.xlsx", "Test2.xlxs" and so on (yes I've got many of these wb's...).
Now what is that all about? Shouldn't this work even when workbooks is closed down?

 

[RoryA]

No - INDIRECT will only work with an open workbook.

 

[kit99]

No - INDIRECT will only work with an open workbook.

Oh..., one more lesson learned!
Any way of doing the same thing without Indirect - and keeping wb's closed down?

 

[RoryA]

Only using something like INDIRECT.EXT in the MOREFUNC add-in or code like Harlan Grove's Pull function. Neither are ideal, in my opinion.

 

[kit99]

Only using something like INDIRECT.EXT in the MOREFUNC add-in or code like Harlan Grove's Pull function. Neither are ideal, in my opinion.

Okey. Thanks a lot for responding to my thread.