Excel hyperlinks

[pommesmitmayo]

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
2	Customer 6
sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=sheet2!$A$2:$A$21

 

[Georgiboy]

Maybe:

Book1.xlsm
	A	B
1	Go there	Customer 8
Sheet1

Cell Formulas
Range	Formula
A1	A1	=HYPERLINK("[Book1.xlsm]Sheet2!"&ADDRESS(MATCH(B1, Sheet2!B2:B11, 0)+1,2), "Go there")

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=Sheet2!$B$2:$B$11

Book1.xlsm
	A	B	C
1
2		Customer 1
3		Customer 2
4		Customer 3
5		Customer 4
6		Customer 5
7		Customer 6
8		Customer 7
9		Customer 8
10		Customer 9
11		Customer 10
Sheet2

 

[pommesmitmayo]

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?

 

[Georgiboy]

You are welcome, thanks for the feedback.

 

[FormR]

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:

=HYPERLINK("#Sheet2!"&ADDRESS(MATCH(B1, Sheet2!B2:B11, 0)+1,2), "Go there")

 

[pommesmitmayo]

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:

=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.