Calling a cell that contains part of a filename

[Renzokuken]

Hi Everyone! Appreciate if you could help me with this:

So here's what I have:

1 2 3

A 01_01_14 01_02_14 01_03_14

B OUTLOOK OUTLOOK OUTLOOK

C OUTLOOK01_01_14 OUTLOOK01_02_14 OUTLOOK01_03_14

D ____ ____ ____

<tbody>
</tbody>

Here's the formula I want to have in D1:

='[OUTLOOK01_01_14.xlsx]Book1'!$X$1

Note:
1. Book 1 is the worksheet from the OUTLOOKMM_DD_YY.xls file.
2. $X$1 is the cell that I want to call from Book 1 of the OUTLOOKMM_DD_YY.xls file.

The aim is to create a program/formula that automatically puts those written in Column C as part of the filename (OUTLOOKMM_DD_YY.xls) in the formula in Column D, so that I can call cell $X$1 from that file.

TIA!

 

[VoG]

Try INDIRECT http://www.contextures.com/xlFunctions05.html

 

[vogel997]

Formula in D1:

=INDIRECT("["&C1&".xlsx]Sheet1!$X$1")

If the file is closed, you need to add the directory and folder, where the file is located.

 

[VoG]

If the file is closed, you need to add the directory and folder, where the file is located.

If the file is closed then INDIRECT will not work.

 

[AlphaFrog]

If the file is closed, use a custom function called PULL

Excel “Pull” Function: Creating dynamic links to closed workbooks

If you do a web search for something like Excel Pull function, you'll find several threads on this topic.

 

[Renzokuken]

Thanks everyone for the quick responses! I forgot to mention that I would prefer a function/formula that will not require me to open each file coz theres a just a LOT :D

Will do search about the pull function.

Again, thanks! If you have other suggestions, please feel free to post here.