超链接到Excel中另一个工作表上的单元格 [英] Hyperlink to a cell on another sheet in Excel
问题描述
在下面的示例中,我在同一个工作簿上有两张。现在,如果我单击Sheet 1上的单元格A1,那么应该在Sheet 2上单击E1。
我通过粘贴专门知道我可以传递超链接,但棘手的部分是Sheet:2上的数据是动态的,所以我不能去那个路线。
在查看Google之后,我发现了很多事情,所以我把它们绑在一起,但是这是错误的引用无效。
以下是我在Sheet 1上单元格A1上使用的公式:
= HYPERLINK( [Sample.xlsx]表:2(ADDRESS(MATCH( ABC,之前,0),MATCH( ABC 之前,0), 1)),ABC)
这里 BEFORE 是一个名字范围覆盖A1到E1。
工作表:1
abcde
1. abc
工作表:2
abcde
1. xxxx abc
欣赏您的输入。
= HYPERLINK [Sample.xlsx]'Sheet2'!& ADDRESS(1,MATCH(abc,BEFORE,0)),ABC)
如果您的工作表名称可能有空格,请确保在名称周围添加单引号。
I am working on Excel report in which I need to pass a link which can take me to a cell referenced.
In below example, I have two sheets on same workbook. Now if I click on cell "A1" on Sheet:1, then it should take me to cell "E1" on Sheet:2.
I know by paste special I can pass hyperlink, but tricky part is data on Sheet:2 is dynamic so I can't go that route.
After looking on Google, I have found out multiple things, so I clubbed them together but it is throwing error "Reference is not valid".
Here is the formula that I've used on cell "A1" on Sheet:1:
=HYPERLINK("[Sample.xlsx]Sheet:2!(ADDRESS(MATCH(""ABC"",BEFORE,0),MATCH(""ABC"",BEFORE,0),1))","ABC")
Here BEFORE is a name range that covers A1 to E1.
Sheet:1
a b c d e
1. abc
Sheet:2
a b c d e
1. x x x x abc
Appreciate your inputs.
=HYPERLINK("[Sample.xlsx]'Sheet2'!" & ADDRESS(1,MATCH("abc",BEFORE,0)),"ABC")
If your sheet name might have spaces then make sure to add the single quotes around the name.
这篇关于超链接到Excel中另一个工作表上的单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!