本文发表在 rolia.net 枫下论坛In Excel, there are formulas with filename's link. What I need to do is to make those links automatically updated when the cell of "date" changed (eg. Cell A100) . For instance,
Cell A1 will load the value from a cell of another file which under the road of:
'R:\WORKGRP\FINANCE\2008\Accounting\102008\P&L Reconciliations\[X - Working - Brampton. Rec. PL Oct.xls]Data'!$E$30
In the above filename's link, it contains year (2008) and month and year (102008),
my goal is: to realize when the year and month changed in cell A100, eg. change from 102008 to 042009, the filename's link will automatically updated as :
'R:\WORKGRP\FINANCE\2009\Accounting\042009\P&L Reconciliations\[X - Working - Brampton. Rec. PL Oct.xls]Data'!$E$30
what I did currently is:
I used a formula and split the filename's link into several parts.
A1 ="'"&A4 & B4 & C4 & D4 & E4 & "[" & F4 & "]" &G4 & "'!" & H4
A4 = ^R:\WORKGRP\FINANCE\
B4 = YEAR(A100)
C4 = ^\Accounting\
D4 = CONCATENATE(MONTH(A1),B4)
E4 = ^\P&L Reconciliations\
F4 = ^X - Working - Brampton. Rec. PL Oct.xls
G4 = ^Data
H4 = ^E30
However in this way, A1 is a string, not a real link. It can only shows the road of file, but can't load the value of that cell.
Experts, Pls. help me to convert the A1 into a real link and grasp the value from the linked file.
Or
Help me to realize my goal in other ways? Highly appreciate your help and have a nice day!更多精彩文章及讨论,请光临枫下论坛 rolia.net
Cell A1 will load the value from a cell of another file which under the road of:
'R:\WORKGRP\FINANCE\2008\Accounting\102008\P&L Reconciliations\[X - Working - Brampton. Rec. PL Oct.xls]Data'!$E$30
In the above filename's link, it contains year (2008) and month and year (102008),
my goal is: to realize when the year and month changed in cell A100, eg. change from 102008 to 042009, the filename's link will automatically updated as :
'R:\WORKGRP\FINANCE\2009\Accounting\042009\P&L Reconciliations\[X - Working - Brampton. Rec. PL Oct.xls]Data'!$E$30
what I did currently is:
I used a formula and split the filename's link into several parts.
A1 ="'"&A4 & B4 & C4 & D4 & E4 & "[" & F4 & "]" &G4 & "'!" & H4
A4 = ^R:\WORKGRP\FINANCE\
B4 = YEAR(A100)
C4 = ^\Accounting\
D4 = CONCATENATE(MONTH(A1),B4)
E4 = ^\P&L Reconciliations\
F4 = ^X - Working - Brampton. Rec. PL Oct.xls
G4 = ^Data
H4 = ^E30
However in this way, A1 is a string, not a real link. It can only shows the road of file, but can't load the value of that cell.
Experts, Pls. help me to convert the A1 into a real link and grasp the value from the linked file.
Or
Help me to realize my goal in other ways? Highly appreciate your help and have a nice day!更多精彩文章及讨论,请光临枫下论坛 rolia.net