Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use a hyperlink to display a selection of cells on another sheet (in same workbook) | Excel Discussion (Misc queries) | |||
Hyperlink to another sheet, same workbook | Excel Discussion (Misc queries) | |||
hyperlink tospecific sheet of html workbook | Excel Worksheet Functions | |||
hyperlink to sheet in workbook saved as html | Excel Discussion (Misc queries) | |||
Hyperlink to specific sheet; workbook saved as html | Excel Worksheet Functions |