Can HLOOKUP have a dynamic filename?

I have a question.

The filename in an hlookup function seems to never accept a dynamic value, like a cell value. For example, if I try to use the following function:

Please Login or Register  to view this content.

and I have a whole bunch of files in the folder named excel1, excel2, excel3, excel4 etc, is there anyway to open one of these files by simply putting a "1" or "2" etc into a specific cell? So instead of putting 'excel1.xlsx' into the function, you would use a value like a cell value that can be changed by the user?

Thank you in advance for any help. I can't seem to figure out how to do this.

You can, using INDIRECT, but only if the file is open. Will that work?

If not, ASAP Utilities has a version of INDIRECT that will work with closed workbooks.

Thanks, I'll look into that ASAP utilities app. That sounds like it will work. Thank you!

I was of the notion that there was a free version, with at least some of the functionality. Have you verified that what you need isn't there?

Quote from ASAP Utilities download page ( http://www.asap-utilities.com/downlo...-utilities.php )

When to buy a license
You should purchase a license and register ASAP Utilities either:
if you use it in a commercial organization (Feel free to try it for 90 days. If you like it, simply purchase a registration key.)
or if you don't want to be forced to update to the new version twice a year
or if you like this program and think it is worth the money

So if you're using it for personal use, it would appear you're not required to purchase a license (although it's recommended if you feel "it's worth the money").

Yeah, it would be for personal use for me, I'm not selling the sheet, but the guy I'm giving the sheet to would be using it for commercial purposes. I do think it's very useful to be able to refer to a closed workbook (especially in something like LOOKUP), but I just ended up hardwiring the cell right to the source file. No flexible LOOKUP filename, just direct path, so that means I can't just duplicate the formula for all 100 files on the master sheet...I need to type the source filename in each cell. I guess 'them's the breaks.' Thanks again.

There is a free addin here called Morefunc:

http://download.cnet.com/Morefunc/30...-10423159.html

that uses the INDIRECT.EXT function which works exactly as the native INDIRECT function..with the exception that it does allow referenced workbook to be closed.