Reference a specific cell for part of workbook name in formula

I am using the following formula to obtain data from a workbook.

Formula:

Please Login or Register  to view this content.

Is it possible to reference a cell value for part of the workbook's name in my formula. For example In the workbook that contains the formula I have the month and year (May 2013 ) in cell G2. So I am looking to do something like the following.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

Is this even possible?

You have to use the INDIRECT formula to reference it:

Formula:

Please Login or Register  to view this content.

That should work

Regards
Duncan

duncandhu, I had originally tried using the INDIRECT formula with no luck. I copied yours and the formula returned #REF!.

Originally Posted by randolphoralph

duncandhu, I had originally tried using the INDIRECT formula with no luck. I copied yours and the formula returned #REF!.

I think this will only work if the file is open. If the file is closed you need to provide the full path to the file.

Originally Posted by duncandhu

I think this will only work if the file is open. If the file is closed you need to provide the full path to the file.

Even with the file open I have had no luck.

Originally Posted by jeffreybrown

@randolphoralph,

Duncan's formula will work, but you need to adjust the workbook extension. Instead of .xslx you need to reverse it to .xlsx

Don't I feel silly...did not even pick up on that. Thanks guys!

Originally Posted by randolphoralph

Don't I feel silly...did not even pick up on that. Thanks guys!

Schoolboy error! Sorry about that!

Originally Posted by randolphoralph

Ok so I have gotten the formula to work with the workbook open. Once I close the workbook the formula no longer calculates. Below is my attempt at adding the location so that the formula works when the source workbook is closed.

Formula:

Please Login or Register  to view this content.

I must be missing something.

Put the whole path inside the square brackets.

Thanks
Duncan

Tried it and no success

Formula:

Please Login or Register  to view this content.

Doing some additional research on good ole google....it appears that the INDIRECT function can not be used to pull data from a closed workbook. Back to square #1

Apologies randolphoralph, it seems my proposed solution will not work for you.

Regards
Duncan