Reputation: 11
I have 2 Excel files named September and October. The September file contains values from which the October file refers/links to using the formula:
=+B2+'C:\\[September.xlsx]Sheet1'!A1
so cell B2
in October contains a value that is the sum of B2
and A1
which comes from the September file.
If I now create a November file, I would simply do a Save As on the October file and save the file as November.xlsx. However, this means that the November file is still referring to values September.xlsx. Is there any way of automatically updating the cells in November.xlsx to refer to October, either upon creation or opening of the November file?
i.e. so November's formula would automatically update to =+B2+'C:\[October.xlsx]Sheet1'!A1
.
Or making a Window form pop up when opening the file, asking the month it would like it to link to, the user would then enter in a month then all the cells in range would be updated.
Is anyone able to point me in the right direction?
Upvotes: 1
Views: 2311
Reputation: 38551
This purely formula-based solution works for me. It is spread across several cells. I imagine you could combine all the formulas in to one very long one in a singe cell, but in my view it's preferable not to. I would just tuck the intermediate cells (cells F12-F17 in my example) away somewhere out of the way.
The formulas:
Cell Formula
---- ---------------------------------------------------------
F12 =CELL("filename",A1)
F13 =MID(F12,FIND("[",F12)+1,FIND("]",F12)-FIND("[",F12)-1)
F14 =LEFT(F13,FIND(CHAR(1),SUBSTITUTE(F13,".",
CHAR(1),LEN(F13)-LEN(SUBSTITUTE(F13,".",""))))-1)
F15 =TEXT(DATE(2000,MONTH(DATEVALUE("1 " & F14))-1,1),"mmmm")
F16 =F15 & MID(F13,FIND(CHAR(1),SUBSTITUTE(F13,".",
CHAR(1),LEN(F13)-LEN(SUBSTITUTE(F13,".","")))),LEN(F13))
F17 =SUBSTITUTE(F12,F13,F16)
F18 =INDIRECT(ADDRESS(1,1,,,F17))
The results, assuming the current sheet is saved as December.xlsx
:
Cell: Value:
----- ---------------------------------------------------------
F12 C:\Users\user.name\Documents\[December.xlsx]Sheet1
F13 December.xlsx
F14 December
F15 November
F16 November.xlsx
F17 C:\Users\user.name\Documents\[November.xlsx]Sheet1
F18 value from cell A1 in November.xlsx!
Notes:
Upvotes: 0
Reputation: 53166
You can use the Workbook.ChangeLink
method.
expression.ChangeLink(Name, NewName, Type)
where Name
is the existing file name, NewName
is the new file name
To see it in action, try recording a macro while changing the link source manually, and examine the resulting code. Access it from Data/Connections/Edit Links
menu
Probably the simplest implementation would be to write a macro to do the SaveAs and ChangeLink in one go. Or leverage the BeforeSave
event.
Upvotes: 2
Reputation: 31394
A simple find and replace will work with this kind of link.
You'll have to skip over cells that use the name as a label that you don't want to change. You can also do this in vba by looking only at formulas. Here is a post about how to do that.
Upvotes: 2
Reputation: 3068
Try recording a macro while you save your October workbook as November.xlsx and then editing the links in the new workbook to link to October.xlsx.
The raw code will be quite messy but you should be able to edit it to suit other months.
Upvotes: 0