Hyperlink for each cell in a column

**mrkake** · 08-13-2012, 12:57 AM

Hello,
I am new to this forum and am self taught in Excel (and the crowd cringes). I have a workbook with 5 worksheets. The first tab is a table of contents for the other 4. Each of these four worksheets consists of four columns (by about 50 rows) of data. The column A consists of numbers starting with 1.01 in cell A3 and on this page goes up to 1.33 in cell A35. Column B contains text that relates to company policies. C and D contain further descriptions and are not really relevant to my question. The same basic format is followed on the remaining worksheets. What I need to do is create a table of contents on page one that mirrors column A and B (I will go back and truncate some of the text in B, but again, not really relevant). I want to place a hyperlink in cell A that links to that same cell (1.12 in the TOC tab links to 1.12 on tab #2) but I don't want to have to do this individually for each hyperlink. That's how I started and it will just take too long. I tried the HYPERLINK formula but I must be missing something because it won't work for me. I need to handle this without using VBA if possible.

I hope that's enough info. I tried to be thorough. Any help is appreciated!

If this has already been answered could someone just post a link to the thread?

**Max, Singapore** · 08-13-2012, 03:14 AM

In your TOC sheet,
Assume the nums (eg: 1.01) are running in A3 down
To hyperlink to the corresponding/matching nums (eg: 1.01) in say Sheet1,
You could try this in B3:
=IF(A3="","",IF(ISNA(MATCH(A3,Sheet1!A:A,0)),"",HYPERLINK("#"&CELL("address",INDIRECT("'Sheet1'!A"&MATCH(A3,Sheet1!A:A,0))),"Sheet1-"&A3)))
Copy B3 down. Modify the friendly name to taste ie this part: "Sheet1-"&A3
Above assumes that the corresponding/matching nums are also found in col A in Sheet1

**mrkake** · 08-13-2012, 04:06 PM

Thanks Max! This has put me on the right track (I think). I haven't had time to test it yet, but I can see the logic in the equation.

One other question; Instead of starting in column B of the TOC, couldn't I place the formula in A3 and change the friendly name to match A3 in Sheet 1 (e.g. "1.01"). The friendly name is what is visible to the casual user correct?

**Max, Singapore** · 08-13-2012, 09:53 PM

> .. One other question; Instead of starting in column B of the TOC, couldn't I place the formula in A3 and change the friendly name to match A3 in Sheet 1 (e.g. "1.01"). The friendly name is what is visible to the casual user correct?

well, you could, but that would mean hardcoding each num (eg: 1.01) in the hyperlink formula, won't it? I framed it up to make use of the original listing of the nums which is already there running in A3 down. For presentation-wise, what could simply be done is you could hide away col A, and leave the clickable hyperlinks in col B visible (adjust the friendly names to taste - yes, this is what is visible to the user)