×

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

@Calgary

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

  • 现在社区游泳池招救生员巡逻，过一会还要不停的点人头，这工作是否很快就被AI代替了，装一个摄像头后面是AI处理系统，这样只招一个等着救人就行了，人头沉下水一定时间立马强光照射，还可以分析水下人的状况来判断是否溺水等
  • 新冠疫苗引发心肌炎? 最新研究这样看
  • 这道几何题有解吗?
  • SAM 2 Demo
  • 如何在 AWS EC2 实例上构建 WordPress 应用程序?
    • 枫下论坛主坛 /  工作学习 /  学科技术讨论