Indirectly Referencing Another Workbook

Hello Everyone!

I'm trying to do something that seems simple, but I can't seem to find any useful information on it.

I don't have an example to attach, so hopefully this is clear. All I want to do is allow the user to enter the file name of another workbook (the file is assumed to already be open) in a cell--we'll say A2--and then have other formulas in the same sheet reference data from the specified woorkbook.

So let's say A2 is populated with "SampleExcelSpreadsheet.xlsx".

In cell C2 I'm looking for a formula like:

Please Login or Register  to view this content.

Where the file name in the formula changes depending on what is in cell A2. Does that make sense? Can it be done?

Thanks much in advance!

RA

Have a look at the INDIRECT() function.
The other file MUST be open for this to work (otherwise you'll need to download an alternative function: INDIRECT.EXT).
Also, the INDIRECT() function is volatile. Read: http://www.decisionmodels.com/calcsecretsi.htm

http://office.microsoft.com/en-us/ex...in=HP010079186

Thank you!

The INDIRECT() function works just fine for my purposes, and I appreciate that you made me figure out how to use it rather than giving me something to cut & paste!

You're welcome. Thanks for the 'star tap'.

Based on your last post it seems that you are satisfied with the solution you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself.
Thanks.

Apologies! I generally remember to do that. In any case it appears that my issue isn't quite solved just yet, and I was hoping you could assist a little bit further.

My project is mostly complete, but the INDIRECT() function seems to work perfectly for some file names, and not for others. The files are all identical in layout and format (it's a tool that we use for each new deal), but the names are different.

I don't understand why some files names are okay and others return a sheet full of #REF errors. Could it have to do with the volatile nature of the function?

Thanks again!

Nevermind, I figured it out...much to my dismay. All of the file names that don't work have appostrophes in them. Apparently the INDIRECT() function doesn't like punctuation!

Unfortunate, but avoidable.