Hyperlink to another sheet within a workbook using formula

[Christopher Watson]

Hey Guys,

Thanks in Advance for your help on this one.

I've got a list about 1500 records long that I want to hyperlink to matching
records on the following page sheet. There are too many to right click and
use the hyperlink tool.

I.E. Customer A on sheet "Total Sales" needs to hyperlink to Customer A on
sheet "By Product".

I tried the following function

=MID(CELL("filename",'By Product'!$A$1),FIND("[",CELL("filename",'By
Product'!$A$1)),256)&"!"

as named formula Psheet2 in the following formula, nested in a hyperlink
formula.

=HYPERLINK(WBsheet2 & ADDRESS(MATCH(B17,'By Product'!B:B,0),(MATCH(B17,'By
Product'!B:B,0))))

where B17 is the location of the customer name, and column B in By Product
contains the matching Customer name

I didn't enter a friendly name at the end, but I dont think that should
matter.

When I try this I get an error message: Reference Not Valid. I think it has
something to do with the fact that both match functions return the same
coordinates, but I'm not sure what should go in there.

Thee other thing I tried was using a match function to find the row number
of the matching customer, (I know the column is B) and then add that into a
hyperlink function, like so...

=HYPERLINK('By Product'!B112)

When I do that, it returns a hyperlink with the name of the proper customer,
but when I open the link, I get an error message, "Cannot open the specified
file.

I tried to add the file name with the original named formula, , and got
something like this...

=HYPERLINK(MID(CELL("filename",'By Product'!$A$1),FIND("[",CELL("filename",
'By Product'!$A$1)),256)&"!"&"J45")

with the cell I want to link to typed in J45 (i.e. B101 is in J 45)


but I keep getting reference is not valid messages.

Any Ideas?

 

[Max]

One play which should deliver this ..

In Total Sales,
Assuming cust names listed in A2 down

Place in B2:
=IF(A2="","",IF(ISNA(MATCH(A2,'By
Product'!B:B,0)),"",HYPERLINK("#"&CELL("address",INDIRECT("'By
Product'!B"&MATCH(A2,'By Product'!B:B,0))),A2)))
Copy down. The above creates hyperlinks in col B which jumps to the matched
name in By Product's col B.

Above is illustrated in this sample:
http://www.flypicture.com/download/MzYzNjA=
Hyperlink matching record in another sht.xls