Excel 2010超链接单元格 [英] Excel 2010 Hyperlinking Cells

问题描述

我在excel文档(工作簿)中有两张纸.它们以相同的排列具有相同数量的单元.第一页包含摘要信息，第二页(在相应的单元格中)包含更详细的信息.

I have two sheets in an excel document (workbook). They have the same number of cells in the same arrangement. The first sheet contains summary information and the second sheet (in the corresponding cell) contains the more detailed information.

我想使工作表超链接，以便一个人可以单击摘要单元格以将其带到详细单元格.

I want to hyperlink the sheets so that, a person can click the summary cell to be brought to the detailed cell.

我知道我可以使用以下公式创建超链接:

I know I can use the following formula to create a hyperlinks:

HYPERLINK(链接位置，[friendly_name])

HYPERLINK(link_location, [friendly_name])

(至少在Excel 2010中)"link_location"条目的格式必须为"#BOOK！Cell"，其中必须包含引号.

And (at least in Excel 2010) the "link_location" entry must be in the format "#BOOK!Cell" where the quotes must be included.

但是，当您单击一个单元格进行选择时，该公式将其简单地处理为BOOK！CELL.可以拖动并以BOOK！CELL格式自动填充其他单元格.但是那时我只有一堆破碎的细胞.关于如何以正确的格式自动填充我的第一张工作表的任何建议?

However, when you click on a cell to make a selection, the formula processes it simply as BOOK!CELL. Its possible to drag and have the other cells auto populate with the BOOK!CELL format. But then I just have a bunch of broken cells. Any advice on how to automatically populate my first sheet with the correct format?

推荐答案

，您需要使用子地址链接到工作簿中的某个位置.我将在下面运行此示例，该示例将更新所有链接以反映其他工作表位置.将工作表的名称更改为您的两个工作表名称.

you need to use the subaddress to link to a place in the workbook. I would run this example below which would update all links to reflect the other sheet location. Change the name of the sheets to your two sheet names.

Sub FixMyLinks()
On Error Resume Next
Dim c As Range
Dim wsSummary As Worksheet
Dim wsDetails As Worksheet

Set wsSummary = ThisWorkbook.Sheets("Sheet1")
Set wsDetails = ThisWorkbook.Sheets("Sheet2")
    For Each c In wsSummary.UsedRange.Cells
        wsSummary.Hyperlinks.Add Anchor:=c, Address:="", SubAddress:=wsDetails.Name & "!" & Replace(c.Address, "$", ""), TextToDisplay:=c.Value
    Next c

End Sub

这篇关于Excel 2010超链接单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！