Formula to link to a cell in a closed workbook

[Ando7p]

Struggling to find a working solution to this one. The scenario :-

Data files are stored each month in a relative folder (the same file name, sheet name and cell reference) so the folder structure looks something like

C:\DataFiles\2011\Jan
C:\Data Files\2011\Feb
C:\Data Files\2011\Mar
.... etc

VBA cannot be used for security - this is not optional in any way - hence why I need the formula.

What I intended to do was, in a summary workbook held on a different drive location, build the filename and path up from the result of several cells, i.e.

A1 has the path - C:\Data Files\ (the path contains spaces which is out of my control and cannot be changed)
A2 contains todays date displayed as a year (YYYY)
A3 contains todays date displayed as a month (MMM)

so in B1 I expected the formula to read something like :-

="'" & A1 & A2 & "\" & A3 & "\[Book1.xls]Sheet1'!Z26" (where Sheet 1 Z26 contains the value I am after)

It displays the correct text string, but does not actually perform the "paste link" style function. The summary file will have many references to closed workbooks, so simply asking the end user to change the cell reference to a different month is a no go.

I have tried inserting an equals sign at the begining of the string, i.e. "='" & A2 etc, but all that happens then is that Excel automatically inserts another ' as the first character in the cell. If I manually remove the preceeding ' the formula then works and displays the required value, therefore I know my string is correct in its syntax.

Any help greatly appreciated.

 

[Ruddles]

You're building a reference to a cell from a closed workbook in B1 of the current worksheet and you want to return the contents of the cell from the closed workbook into the current worksheet - yes?

Normally you would use the INDIRECT function to do 'that sort of thing' but unfortunately INDIRECT does not work on closed workbooks: http://support.microsoft.com/kb/151323.

You say "VBA cannot be used for security". Okay, maybe not in the workbook itself, but would you or some other trusted person be permitted to use VBA remotely to carry out an automated edit of the workbook? I mean, open the workbook, edit all the references and close it again, using VBA?

 

[Ando7p]

You highlighted my plight! The 'master' summary workbook is a front end tool used by many different people with the results displayed based on their filter selections, therefore can be wide and varied, so having just one person who can open up all the files and 'update links' is not a viable option.

I looked to using Indirect, but not good for the reasons stated.

Doing via VBA is so easy, but I just cannot employ this method. Not sure that the request is even possible.

Keep the suggestions coming please .....

 

[RoryA]

If you can't use VBA for security reasons (personally, I would laugh in the face of any IT department that said that to me) then you probably can't download and use addins such as MOREFUNC which provides an INDIRECT.EXT function for this purpose? If not, then you are probably out of luck, other than using the edit links dialog to change the source workbook, or if you can use XLM macros.

 

[Ando7p]

The issue is not being able to use VBA, it is the application environment in which the Excel will reside. Our company IT is so far behind anything remotely like the rest of the world that opening Excel from the internet uses Internet Explorer 6 as the parent app, which has issues with VBA.

Too many users (who can't / don't / won't) force Excel to open from IE into Excel pukka, so hence having to resort to trying to use a 'simple' formula to resolve the constraints. Indirect requires the source to be open to update -so no good.

I can load external resources / add-ins as they will reside in the file itself so should not pose a problem.

By the time I'm done trying to fix this one, I may as well have sucked all the data back into Access and just used some predertimed queries!

And before you say it, not all users have Access nor will the licenses be made available (plus we have too many platforms - Win 2k, XP Home, Pro and 64 bit, Vista and Win7, Office 97, 2000, 2002,2003 and 2007)! Groan .....