Reference to File Name

[rudevincy]

Please someone help!

I have a 2 workbooks in Book1.xlsx cell B2 it says

='C:\Libraries\Documents\[Book2.xlsx]Oct ''09'!$L$96

my thing is that I want instead of saying Oct ''09' I want it to refer to cell A1 (A! contains the name of the tab)

Also Book2.xlsx is closed and I have been told that I cannot use INDIRECT with closed workbooks..... it returns a #REF

is it possible to do this? PLEASE HELP!!

 

[Andrew Poulsom]

You can try INDIRECT.EXT in the MoreFunc Add-In.

 

[rudevincy]

The MoreFunc Add-In where do I get that?

 

[Andrew Poulsom]

Google is your friend.

 

[rudevincy]

ADVERTISEMENT

I have been Googling and all I am finding is articles about how it does not function in Excel 2010

 

[Andrew Poulsom]

Why don't you download it and try it? If it doesn't work you can always Edit Links and change the Source. That could be automated with a macro.

 

[rudevincy]

Question after downloading this.... if another user wants to use the file they have to download this add in as well?

 

[Andrew Poulsom]

In versions of Excel prior to Excel 2007 it's possible to include the Add-In in a workbook. Currently that feature isn't available in Excel 2007 and above. So the user will need to install the Add-In.