Link to cell dependent on date

[nickwilshaw]

Hi All,

First time posting here so please be gentle

I need data to return(link) to cells in excel dependent on the date in the top of that column. This will change as time goes by as the report is to give information about our last 4 management accounts.

What I would most like is some way where I can have =x:\accounts\year X\accounts file..... so that if it is 2010 in the top of the column, then Excel will link to year 2010 folder by inserting number 10 from, say &TEXT(c1,"yy") in the link.

The worst case scenario, is I will only be able to do this for the next 7 "iterations" using a nested if statement.

I can't use VLOOKUP or INDEX MATCH with a whole load of dates somewhere else as I need to return a link, not a value. Also, I am reluctant to use the EVAL function as I want this document to be picked up by anyone who needs it without having complicated add-ons.

Any ideas/suggestion would be greatly appreciated.

All the best,


Nick

 

[GlennUK]

You can only link indirectly like that if you have the source files open at the same time, or use an add-in. Or program some VBA to go and fetch the data. Can you have the relevant source files open at the same time?

 

[nickwilshaw]

The best bet might be to use some VBA.

Should I use the VBA to just open the files or can I use it to link the cells and populate accordingly. If the latter, might you be able to indicate the coding required remembering of course that this is going to need to change depending on the actual date that the report is opened.

Many thanks,

 

[nickwilshaw]

Hi,

Forgot to mention....... It would be best if the cells could be poulated without the user having to open the source docs.

Ta,

 

[Steeviee]

Here is the code I use to set up a link (to a week number rather than a year but the principle is the same) where I have the week number in cell C2:

z = Application.WorksheetFunction.Text(Sheets(1).Range("C2").Value, "00")
a = "'G\2011\Power\Wk " & z & "\[C2 Supplier Performance WK " & z & ".xls]"
Range("G11").Formula = "=& a & "Sheet1'!$AE$2

HTH

PS the linked sheets do not need to be open for this link to work.

 

[nickwilshaw]

Hi,

Thanks for that.

Just trying this and get a Compile Error: Expected end of statement

Just wondered if there might be some syntax missing?

Thanks,

 

[Steeviee]

you're not wrong.

Last line should read:

Range("G11").Formula = "=" & a & "Sheet1'!$AE$2"

Sorry for the confusion.

 

[nickwilshaw]

Thanks a million - that works a treat!