Excel通过公式动态超链接
我正在尝试创建从一个工作表到另一个工作表的超链接。该工作表很大，并且有许多超链接指向其他工作表中的各个位置，因此我尝试使用可以复制/粘贴的公式来执行此操作。
这是我所拥有的：
=HYPERLINK(CELL("address",INDEX('Test Results'!A:A,MATCH("TestCase-001",'Test Results'!A:A,0))),"TestCase-001")
它评估的 URI 是： '[Test Results 1.xls]Test Results'!$A$17 和 上的 工作表确实是要链接到的正确单元格。问题是，当我单击超链接以便测试案例详细信息时，我收到一条错误消息“无法打开指定的文件”，而且我不确定为什么，因为 URI 看起来是正确的。有什么想法可以帮助我解决这个问题吗？A17测试结果
使用 Excel 2003。
I am trying to create a hyperlink from one worksheet to another. The worksheet is large and there are many hyperlinks to places all over the other worksheet so I am trying to do this with a formula that I can copy/paste.
Here's what I have:
=HYPERLINK(CELL("address",INDEX('Test Results'!A:A,MATCH("TestCase-001",'Test Results'!A:A,0))),"TestCase-001")
The URI that it evaluates to is: '[Test Results 1.xls]Test Results'!$A$17, and A17 on the Test Results worksheet is indeed the correct cell to link to. The problem is when I click the hyperlink so I can just to test case details, I get an error saying "Cannot open the specified file", and I'm not sure why since the URI looks correct. Any ideas out there to help me fix this?
Using Excel 2003.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
CELL("address",... 函数以 '[workbook]worksheet'!cell (如您所说) 格式返回，
但是，HYPERLINK 需要 [workbook]'worksheet'!cell (注意 ' 标记的位置)，其中工作表名称包含空格。
最简单的答案是从工作表名称中删除空格...
The CELL("address",... function returns in the format '[workbook]worksheet'!cell (as you say)
However, HYPERLINK expects [workbook]'worksheet'!cell (note the position of the ' marks) where worksheet name includes spaces.
Simplest answer would be to remove the spaces from the sheet names...
弄清楚为什么这两个函数不能一起工作是一项伟大的侦探工作！
有了这些知识，我实际上找到了一种方法来强制将 CELL 函数的响应格式化为 HYPERLINK 函数期望/要求的格式：
如果没有完整的引号，我的 Office 版本（2010）不断截断工作簿和工作表名称，因为它们引用的是代码本身所在的同一张表被写入.
Figuring out why those two functions wouldn't work together was some great detective work!
Armed with that knowledge, I actually found a way to force the formatting from the response of the CELL function into the format that the HYPERLINK function expects/requires:
Without the full quotes, my version of office (2010) kept truncating the Workbook and Worksheet names because they were referring to the same sheet the code itself was being written in.