×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

@Florida

Help needed! VB expert or Excel Macro expert, pls. help to solve a problem......

wispylilac(人生若只如初见)
本文发表在 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
(#4857979@0)
Last Updated: 2008-11-20
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / 学科技术讨论 / Help needed! VB expert or Excel Macro expert, pls. help to solve a problem...... -wispylilac(人生若只如初见); 2008-11-20 {1485} (#4857979@0)
    本文发表在 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
    • =HYPERLINK(CONCATENATE("#",C2,"!","$",C3,"$",C4),"LINK") -kingsw(Steven); 2008-11-20 (#4858734@0)
      • 常规语法： [#[<workbook>]<sheetname>!<range> 示例： #[Parts.xlsx]Sheet1!A1 -kingsw(Steven); 2008-11-20 (#4858748@0)
      • thank you. However this is not what I want. Hyperlink returns the designated text or value, but not the linked value. -wispylilac(人生若只如初见); 2008-11-21 (#4862229@0)

More Topics

  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
  • 扔硬币的实验研究
    • 枫下论坛主坛 /  工作学习 /  学科技术讨论