Hyperlink to another sheet within a workbook using formula
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? |
Hyperlink to another sheet within a workbook using formula
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",I NDIRECT("'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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Christopher Watson" wrote in message . .. 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? |
All times are GMT +1. The time now is 10:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com