Automatic solution for hyperlink within the same file

[Newbie73]

Hi everyone, I'm dealing with huge amounts of date across different sheets in the same file. On sheet1 let's say I have a full list of values (some of them duplicated) in the same column and on Sheet2 I have a list of some (not all) of those values as unique values. Manually, I can create a hyperlink say on Sheet2 A2 and manually add a reference on Sheet1 where the same value is (let's say H777). Clicking now on Sheet2 A2 Value will take me to the same value on Sheet1 H777.

Is there a way to automatically do this on a column? Automatically look for where the exact value is on Sheet1 column and create an hyperlink to that location?

Test example:

test3.xlsx

1drv.ms

Thanks in advance

 

[etaf]

the link would not work for me
do you want a link to the values - or just bring the value across to the sheet

 

[Newbie73]

the link would not work for me
do you want a link to the values - or just bring the value across to the sheet

Thanks for the reply etaf. Sorry about that, not sure why it's not working. I've uploaded it again:

test4.xlsx

1drv.ms

A link to the values please so if I click on Sheet2, A2 value 1979 will take me directly to Sheet1, H777 value 1979 (this numbers and cell references are just as example)

 

[etaf]

it may be me , but not had trouble before - i just get
excel.officeapps.live.com’s server IP address could not be found.

 

[Newbie73]

it may be me , but not had trouble before - i just get
excel.officeapps.live.com’s server IP address could not be found.

Don't know sorry, I will copy paste just so that you can get an idea.

Sheet2:

1979
2308
2127
2222
1881
1919
1958
2023

Sheet1:

1979
1979
1979
1979
1979
1979
1979
1979
1979
1979
1980
1980
1980
1980
1980
1980
1980
1980
1980
1980
2011
2011
2011
2011
2011
2011
2011
2011
2011
2011
2012
2012
2012
2012
2012
2012
2012
2012
2012
2012
2023
2023
2023
2023
2023
2023
2023
2023
2023
2023

 

[etaf]

how about
=IFERROR(HYPERLINK("#sheet2!"&ADDRESS(MATCH(Sheet1!A1,Sheet2!$A$1:$A$25,0),1)),"No Link")

Book5
	A	B
1	1979	#sheet2!$A$1
2	1979	#sheet2!$A$1
3	1979	#sheet2!$A$1
4	1979	#sheet2!$A$1
5	1979	#sheet2!$A$1
6	1979	#sheet2!$A$1
7	1979	#sheet2!$A$1
8	1979	#sheet2!$A$1
9	1979	#sheet2!$A$1
10	1979	#sheet2!$A$1
11	1980	No Link
12	1980	No Link
13	1980	No Link
14	1980	No Link
15	1980	No Link
16	1980	No Link
17	1980	No Link
18	1980	No Link
19	1980	No Link
20	1980	No Link
21	2011	No Link
22	2011	No Link
23	2011	No Link
24	2011	No Link
25	2011	No Link
26	2011	No Link
27	2011	No Link
28	2011	No Link
29	2011	No Link
30	2011	No Link
31	2012	No Link
32	2012	No Link
33	2012	No Link
34	2012	No Link
35	2012	No Link
36	2012	No Link
37	2012	No Link
38	2012	No Link
39	2012	No Link
40	2012	No Link
41	2023	#sheet2!$A$8
42	2023	#sheet2!$A$8
43	2023	#sheet2!$A$8
44	2023	#sheet2!$A$8
45	2023	#sheet2!$A$8
46	2023	#sheet2!$A$8
47	2023	#sheet2!$A$8
48	2023	#sheet2!$A$8
49	2023	#sheet2!$A$8
50	2023	#sheet2!$A$8
51		No Link
Sheet1

Cell Formulas
Range	Formula
B1:B51	B1	=IFERROR(HYPERLINK("#sheet2!"&ADDRESS(MATCH(Sheet1!A1,Sheet2!$A$1:$A$25,0),1)),"No Link")

 

[Newbie73]

how about
=IFERROR(HYPERLINK("#sheet2!"&ADDRESS(MATCH(Sheet1!A1,Sheet2!$A$1:$A$25,0),1)),"No Link")

Book5
	A	B
1	1979	#sheet2!$A$1
2	1979	#sheet2!$A$1
3	1979	#sheet2!$A$1
4	1979	#sheet2!$A$1
5	1979	#sheet2!$A$1
6	1979	#sheet2!$A$1
7	1979	#sheet2!$A$1
8	1979	#sheet2!$A$1
9	1979	#sheet2!$A$1
10	1979	#sheet2!$A$1
11	1980	No Link
12	1980	No Link
13	1980	No Link
14	1980	No Link
15	1980	No Link
16	1980	No Link
17	1980	No Link
18	1980	No Link
19	1980	No Link
20	1980	No Link
21	2011	No Link
22	2011	No Link
23	2011	No Link
24	2011	No Link
25	2011	No Link
26	2011	No Link
27	2011	No Link
28	2011	No Link
29	2011	No Link
30	2011	No Link
31	2012	No Link
32	2012	No Link
33	2012	No Link
34	2012	No Link
35	2012	No Link
36	2012	No Link
37	2012	No Link
38	2012	No Link
39	2012	No Link
40	2012	No Link
41	2023	#sheet2!$A$8
42	2023	#sheet2!$A$8
43	2023	#sheet2!$A$8
44	2023	#sheet2!$A$8
45	2023	#sheet2!$A$8
46	2023	#sheet2!$A$8
47	2023	#sheet2!$A$8
48	2023	#sheet2!$A$8
49	2023	#sheet2!$A$8
50	2023	#sheet2!$A$8
51		No Link
Sheet1

Cell Formulas
Range	Formula
B1:B51	B1	=IFERROR(HYPERLINK("#sheet2!"&ADDRESS(MATCH(Sheet1!A1,Sheet2!$A$1:$A$25,0),1)),"No Link")

Etaf that did the trick!! Amazing!!! Couple of little questions to see if it can be adjusted, formula worked as intended, this is the one I'm using in the real spreadsheet:
=IFERROR(HYPERLINK("#Main!"&ADDRESS(MATCH(Lumps!G5,Main!$S$2:$S$2500,0),1)),"No Link")

If I click on the link, it will take me to the row above the real cell. For example for Cell Value 2025 I click and it takes me to the cell with 2024 in the "Main" sheet. It's just a detail as I just have to scroll once down and i'm there, but just wondering if it could be adjusted.

Second little detail (sorry if I'm being anal), any chance we could chance the text that shows in the link cell? #Main!$A$1132 for something else? Or even better, is it even possible to add such a formula to the cell with the number itself? As in, the cell with the value to be looked at "Main", for example DFHS2728272506, could that be the cell with the link? Or is this impossible? If not just happy enough if It's in a different cell but with a different name that I could choose than #Main!$A$1132, no worries if not this was already a massive help for my productivity!

 

[etaf]

its returning the row number
=MATCH(G5,$S$2:$S$2500,0)
so as you start from row 2
it sees that as the nth item - but counting from row 2
so if it finds it in say Row 3 - that is 2 from the start of the range - S2

change the range to start at 1
=IFERROR(HYPERLINK("#Main!"&ADDRESS(MATCH(Lumps!G5,Main!$S$1:$S$2500,0),1)),"No Link")

now you should be on the correct row

I'll reply later re other question, may take longer to work out - then 10mins to edit here

 

[etaf]

ok,
there is a friendly name option in the hyper link function
so i have added the cell here

=IFERROR(HYPERLINK("#sheet2!"&ADDRESS(MATCH(Sheet1!A1,Sheet2!$A$1:$A$25,0),1),A1),"No Link")
see the match is A1
and the friendly name i have added A1 - so that should put the value you are trying to match

Column D

Book5
	A	B	C	D
1	1979	#sheet2!$A$1		1979
2	1979	#sheet2!$A$1		1979
3	1979	#sheet2!$A$1		1979
4	1979	#sheet2!$A$1		1979
5	1979	#sheet2!$A$1		1979
6	1979	#sheet2!$A$1		1979
7	1979	#sheet2!$A$1		1979
8	1979	#sheet2!$A$1		1979
9	1979	#sheet2!$A$1		1979
10	1979	#sheet2!$A$1		1979
11	1980	No Link		No Link
12	1980	No Link		No Link
13	1980	No Link		No Link
14	1980	No Link		No Link
15	1980	No Link		No Link
16	1980	No Link		No Link
17	1980	No Link		No Link
18	1980	No Link		No Link
19	1980	No Link		No Link
20	1980	No Link		No Link
21	2011	No Link		No Link
22	2011	No Link		No Link
23	2011	No Link		No Link
24	2011	No Link		No Link
25	2011	No Link		No Link
26	2011	No Link		No Link
27	2011	No Link		No Link
28	2011	No Link		No Link
29	2011	No Link		No Link
30	2011	No Link		No Link
31	2012	No Link		No Link
32	2012	No Link		No Link
33	2012	No Link		No Link
34	2012	No Link		No Link
35	2012	No Link		No Link
36	2012	No Link		No Link
37	2012	No Link		No Link
38	2012	No Link		No Link
39	2012	No Link		No Link
40	2012	No Link		No Link
41	2023	#sheet2!$A$8		2023
42	2023	#sheet2!$A$8		2023
43	2023	#sheet2!$A$8		2023
44	2023	#sheet2!$A$8		2023
45	2023	#sheet2!$A$8		2023
46	2023	#sheet2!$A$8		2023
47	2023	#sheet2!$A$8		2023
48	2023	#sheet2!$A$8		2023
49	2023	#sheet2!$A$8		2023
50	2023	#sheet2!$A$8		2023
51		No Link		No Link
Sheet1

Cell Formulas
Range	Formula
B1:B51	B1	=IFERROR(HYPERLINK("#sheet2!"&ADDRESS(MATCH(Sheet1!A1,Sheet2!$A$1:$A$25,0),1)),"No Link")
D1:D51	D1	=IFERROR(HYPERLINK("#sheet2!"&ADDRESS(MATCH(Sheet1!A1,Sheet2!$A$1:$A$25,0),1),A1),"No Link")

 

[Newbie73]

ok,
there is a friendly name option in the hyper link function
so i have added the cell here

=IFERROR(HYPERLINK("#sheet2!"&ADDRESS(MATCH(Sheet1!A1,Sheet2!$A$1:$A$25,0),1),A1),"No Link")
see the match is A1
and the friendly name i have added A1 - so that should put the value you are trying to match

Column D

Book5
	A	B	C	D
1	1979	#sheet2!$A$1		1979
2	1979	#sheet2!$A$1		1979
3	1979	#sheet2!$A$1		1979
4	1979	#sheet2!$A$1		1979
5	1979	#sheet2!$A$1		1979
6	1979	#sheet2!$A$1		1979
7	1979	#sheet2!$A$1		1979
8	1979	#sheet2!$A$1		1979
9	1979	#sheet2!$A$1		1979
10	1979	#sheet2!$A$1		1979
11	1980	No Link		No Link
12	1980	No Link		No Link
13	1980	No Link		No Link
14	1980	No Link		No Link
15	1980	No Link		No Link
16	1980	No Link		No Link
17	1980	No Link		No Link
18	1980	No Link		No Link
19	1980	No Link		No Link
20	1980	No Link		No Link
21	2011	No Link		No Link
22	2011	No Link		No Link
23	2011	No Link		No Link
24	2011	No Link		No Link
25	2011	No Link		No Link
26	2011	No Link		No Link
27	2011	No Link		No Link
28	2011	No Link		No Link
29	2011	No Link		No Link
30	2011	No Link		No Link
31	2012	No Link		No Link
32	2012	No Link		No Link
33	2012	No Link		No Link
34	2012	No Link		No Link
35	2012	No Link		No Link
36	2012	No Link		No Link
37	2012	No Link		No Link
38	2012	No Link		No Link
39	2012	No Link		No Link
40	2012	No Link		No Link
41	2023	#sheet2!$A$8		2023
42	2023	#sheet2!$A$8		2023
43	2023	#sheet2!$A$8		2023
44	2023	#sheet2!$A$8		2023
45	2023	#sheet2!$A$8		2023
46	2023	#sheet2!$A$8		2023
47	2023	#sheet2!$A$8		2023
48	2023	#sheet2!$A$8		2023
49	2023	#sheet2!$A$8		2023
50	2023	#sheet2!$A$8		2023
51		No Link		No Link
Sheet1

Cell Formulas
Range	Formula
B1:B51	B1	=IFERROR(HYPERLINK("#sheet2!"&ADDRESS(MATCH(Sheet1!A1,Sheet2!$A$1:$A$25,0),1)),"No Link")
D1:D51	D1	=IFERROR(HYPERLINK("#sheet2!"&ADDRESS(MATCH(Sheet1!A1,Sheet2!$A$1:$A$25,0),1),A1),"No Link")

Thanks a lot Etaf! Really appreciate the time you've taken to help