Hyperlink Using MATCH

[lms]

All I want to do is hyperlink to a cell in the same sheet as the hyperlink equation based on the output of the MATCH function. My equation is this: =HYPERLINK(MATCH("B", A:A, 0), "Phase B"). When I just type in the match function it returns the correct row, but with the hyperlink it takes me to cell A1. I've also tried variations of =HYPERLINK("Test Procedures!A"&MATCH("B",A:A, 0), "Phase B") but it won't even accept that when I hit Enter.

I DON'T GET IT!! I'm pretty good with macros and other functions, but I've never tried this before and just can't get it to work.

 

[njimack]

=HYPERLINK("[Book1.xlsx]A"&MATCH("B",A:A,0), "Phase B")
Change Book1.xlsx to the name of your file

 

[lms]

That's really the only way to do it? The name of this file is changing daily (config control for the group using it, we're appending it with the date). Is there any way to make that link update when you save as? Changing all those links each time almost makes the hyperlink convenience not worth it...

(Thanks for the quick reply though, I appreciate it)

 

[njimack]

You could enter the filename into a cell and then refer to that using INDIRECT. Then use a worksheet change event to update the cell containing the filename every time the file is saved.
I'll post back later with an example.

 

[njimack]

Excel Workbook
	A	B
1	A	",CELL("filename"))-FIND("("filename"))-1)]Book2.xlsx
2	B
3	C
4	D
5	E	Phase B
Test Procedures