Excel：自动超链接到另一个单元格

发布于 2024-12-11 01:39:25 字数 366 浏览 0 评论 0原文

我有两列，一列是自动生成的（B 列），另一列（D 列），其中自动生成的列很少有手动值。

B           D
--------------------
1  Col1    Col2
2  12       14
3  13       16 
4  14
5  15
6  16
--------------------

当我在其中输入新行值时，我想自动超链接 D 列。例如条目 D2 应该是 =HYPERLINK("#B4", B4)

现在我可以用 INDEX & 计算 B4 MATCH，但我如何自动超链接它？也就是说，如果我在 D2 中输入 14，它应该会自动替换为 =HYPERLINK("#B4, B4)。

原文

I have two columns, one automatically generated (Column B), the other(Column D) with few manual values from automated generated column .

B           D
--------------------
1  Col1    Col2
2  12       14
3  13       16 
4  14
5  15
6  16
--------------------

I want to automatically Hyperlink Column D, when i enter a new row value in it.
e.g. The entry D2 should be =HYPERLINK("#B4", B4)

Now i can calculate B4 with INDEX & MATCH, but how do i automatically hyperlink it? That is to say, if i enter 14 in D2, it should automatically get replaced by =HYPERLINK("#B4, B4).

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

若相惜即相离 2024-12-18 01:39:25

您必须使用事件过程Worksheet_Change：请参阅此 ozgrid 上的文章或Chip Pearson 网站上的这篇文章。

就像：

Private Sub Worksheet_Change(ByVal Target As Range)
   'Do nothing if more than one cell is changed or content deleted
   If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
   'Trigger the procedure only for the column D
   If Not Intersect(Target, Range("D:D")) Is Nothing Then
     'Turn off ALL events so the Target change does not trigger another time this sub
      Application.EnableEvents = False
      'Change the formula for what you ever want
       Target.Formula = "=HYPERLINK(""#B4"", B4)"
      'Turn events back on
      Application.EnableEvents = True
   End If
End Sub

你只需要改变你想要构建的公式。

You have to use an event procedure Worksheet_Change: see this article on ozgrid or this one on Chip Pearson's website.

Something like:

Private Sub Worksheet_Change(ByVal Target As Range)
   'Do nothing if more than one cell is changed or content deleted
   If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
   'Trigger the procedure only for the column D
   If Not Intersect(Target, Range("D:D")) Is Nothing Then
     'Turn off ALL events so the Target change does not trigger another time this sub
      Application.EnableEvents = False
      'Change the formula for what you ever want
       Target.Formula = "=HYPERLINK(""#B4"", B4)"
      'Turn events back on
      Application.EnableEvents = True
   End If
End Sub

You just have to change the formula you want to build.

回复收藏 0 原文

~没有更多了~