Conditional Hyperlinking

[MikeG]

I have a one-column table, "Table A", which has product names in. Depending on the product that a user selects in that table, I would like to hyperlink to another table that has more details about the project.

There are 20 products and each of these has a seperate details table

What I would like in Table A is to add another column to the right and on each row it would say "Go To Details" then if the user clicks on this cell, they will be taken to the relevent product details table for the relevent product.

For example, cell A4 of Table A might contain Product D, so if the user clicks on cell B4, they will hyperlink to the details table for Product D.

One problem is that I cannot use a macro - the company the users work for won't allow VBA code.

Can a hyperlink be based on a "conditional" value?

Can anyone help?

Thanks,

MikeG

 

[pgc01]

Hi Mike

You can use the Hyperlink() function to do the conditional hyperlinking.

Build a table with the values and respective hyperlinks addresses. In the formula in B use the table to look up the value in A to get the hyperlinks.

 

[MikeG]

Great! Thanks - I wasn't aware of that function.

Mike

 

[MikeG]

Actually, I can't seem to use the Hyperlink function to do what I want.

For example, Cell A6 of my Table A might have Product B, and I can see how to put a Hyperlink() formula in B6 that would jump to the detailed table for Product B. But on other occasions, Cell A6 might be "Product F" - so I would like Cell B6 to have a dynamic formula that will now go to the detailed table for Product F, without me having to change the B6 formula. i.e. more of a lookup type of thing than a hard-coded address.

Can this be done using the Hyperlink() function? Is there a work-around?

Thanks,

MikeG

 

[Sal Paradise]

Like this:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">AAA</td><td style=";">Link Address</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Dropdown</td><td style="font-weight: bold;;">Link</td><td style="text-align: right;;"></td><td style=";">BBB</td><td style=";">Link Address</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">AAA</td><td style="text-decoration: underline;color: #0000FF;;">Click for Details</td><td style="text-align: right;;"></td><td style=";">CCC</td><td style=";">Link Address</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=HYPERLINK(<font color="Blue">INDEX(<font color="Red">E:E,MATCH(<font color="Green">A3,D:D,0</font>),</font>),"Click for Details"</font>)</td></tr></tbody></table></td></tr></table><br />

Just change the "Link Address" to whatever link you want if that option is selected.

 

[MikeG]

Thanks Sal - I'm getting there.

Let's say that if the product is AAA, then I want the user to jump to Cell W6 in the current worksheet. Could you tell me what I put as the link address in Cell E1 to make this happen.

Thanks,

Mike

 

[Sal Paradise]

Depends on your workbook name:
http://office.microsoft.com/en-in/excel-help/hyperlink-HP005209116.aspx

=HYPERLINK("[Budget]June!E56", E56)

To jump to a different sheet in the same workbook, change the name of the sheet in the link. In the previous example, to create a link to cell E56 on the September sheet, change the word "June" to "September."

 

[MikeG]

Depends on your workbook name:
http://office.microsoft.com/en-in/excel-help/hyperlink-HP005209116.aspx

Thanks,

I typed =HYPERLINK("[Book3]Sheet2!E56", E56) in E1 and if I hit this Hyperlink in B1, sure enough I go to cell E56.

However, when I hit the hyperlink in B3, it says "The address of this site is not valid" and so it is not "forwarding" me to E56 via B3.

Mike

 

[MikeG]

Perhaps you could help me this way.

Let's say in cell A1 I type the row that I want to go to in the current worksheet - e.g. 12
In cell A2, I type the column I want to go to e.g. 26

Let's say the current worksheet is Sheet1 and the workbook is Book1.

In A3, I would like a hyperlink formula that will take me to the values specified in A1 and A2 - so in this example, I would jump to cell Z12. If I then typed a 4 in cell A2 and then hit the hyperlink I would go to D12 etc.

Thanks,

MikeG

 

[Sal Paradise]

Sorry, it looks like you need a # sign in front of cell references:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">AAA</td><td style=";">#Sheet1!C1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Dropdown</td><td style="font-weight: bold;;">Link</td><td style="text-align: right;;"></td><td style=";">BBB</td><td style=";">#Sheet1!C2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">BBB</td><td style="text-decoration: underline;color: #0000FF;;">Click for Details</td><td style="text-align: right;;"></td><td style=";">CCC</td><td style=";">#Sheet1!C3</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B3</th><td style="text-align:left">=HYPERLINK(<font color="Blue">INDEX(<font color="Red">E:E,MATCH(<font color="Green">A3,D:D,0</font>),</font>),"Click for Details"</font>)</td></tr></tbody></table></td></tr></table><br />

Microsoft help is useless.