Indirect for Workbook Name

[nms2130]

Good afternoon,
Is it possible to use an indirect or similar function to look at different workbooks, as opposed to different tabs?

I want to be able to type in a workbook name in cell A1 which will tell a formula in B1 to look in the workbook which has the name that was typed into A1. I am familiar with using an indirect reference to accomplish this in searching different tabs within a workbook, however, I cannot figure out how to search through a different workbook.
This is the indirect that I have been using.

INDIRECT("'[nms2130.xls]" & $A$1 & "'!$c$27:$iv$27")

I would like to be able to type in nms2130 into A1 and have the formula in B1 refererence nms2130.xls or if I type nms2131 into A1, B1 references nms2131.xls

Any help would be greatly appreciated.

[EdMac]

Hi,

Indirect will only work with open workbooks - will this be a problem?

[nms2130]

Yes. I was going to use a macro to open up the workbook, but the file name won't change in the macro will it? Do you have any suggestions?

[Richard Schollar]

The argument passed to INDIRECT is a string - so as long as you build up the string so that it correctly reflects what the reference would be, then you will have no troubles. So, you an use:

=INDIRECT("'[" & A1 & ".xls]Sheet1'!A1")

to refer to the workbook name (without extension) given in A1. As noted before, the target workbook must be open, unless you use some VBA or an add-in like morefunc where you can use the function INDIRECT.EXT which works in exactly the same way as INDIRECT but on closed source workbooks.

Richard

[EdMac]

Ah, looks like Richard has got in before me - can't argue with his advice.

[nms2130]

I appreciate your help.