Excel hyperlinks

pommesmitmayo

New Member
Joined
Jun 12, 2011
Messages
23
Hi all, hope well.

I've been trying to get something done with a hyper link that so far has evaded me whether I try with the built in insert function or the formula method.
Description;
I have a spreadsheet and in cell A1 of 'sheet1' tab is where I want to place my hyperlink.
Cell A2 is the cell I use for data validation that sets up some other aspects of the sheet.
The Database tab is where I have a range of info about my clients.

To paraphrase I want the hyperlink cell to take me to a cell that changes depending on the selection in sheet A2.
So. If I have customer 6 selected and click the hyperlink, it takes me to cell B7 in sheet2.
But if I have customer 12 selected and click that same hyperlink, it takes me to cell B13 in sheet2.

Is this possible without using VBA or something exotic?

Thanks.

Test.xlsx
A
2Customer 6
sheet1
Cells with Data Validation
CellAllowCriteria
A2List=sheet2!$A$2:$A$21
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Maybe:
Book1.xlsm
AB
1Go thereCustomer 8
Sheet1
Cell Formulas
RangeFormula
A1A1=HYPERLINK("[Book1.xlsm]Sheet2!"&ADDRESS(MATCH(B1, Sheet2!B2:B11, 0)+1,2), "Go there")
Cells with Data Validation
CellAllowCriteria
B1List=Sheet2!$B$2:$B$11


Book1.xlsm
ABC
1
2Customer 1
3Customer 2
4Customer 3
5Customer 4
6Customer 5
7Customer 6
8Customer 7
9Customer 8
10Customer 9
11Customer 10
Sheet2
 
Upvote 0
Hi Georgiboy, thanks for coming back so quickly. So.........

I've tried as you've suggested an it works beautifully. Many thanks.
Is there anything Excel can't do?
 
Upvote 0
Hi, FWIW, you can also use the # symbol in place of the workbook name when linking to the same workbook that contains the formula. Makes it a little less hardcoded.

For example:
Excel Formula:
=HYPERLINK("#Sheet2!"&ADDRESS(MATCH(B1, Sheet2!B2:B11, 0)+1,2), "Go there")
 
Upvote 0
Hi, FWIW, you can also use the # symbol in place of the workbook name when linking to the same workbook that contains the formula. Makes it a little less hardcoded.

For example:
Excel Formula:
=HYPERLINK("#Sheet2!"&ADDRESS(MATCH(B1, Sheet2!B2:B11, 0)+1,2), "Go there")
Yep, not a bad suggestion.
I'm trying to learn the behemoth that is Excel at the moment so I try and learn the hard way sometimes so you don't forget how to do it. But if honest I don't think I'll ever get to the stage that some of you guys are at.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top