Search code examples
excel-2007

Find cell address and add a hyperlink based on a tab name

I have a multiple tab (200+) workbook where each tab has a specific 3-5 digit name. Tab's name goes into A1 of each respective tab via formula =RIGHT(CELL("filename";A1);LEN(CELL("filename";A1))-FIND("]";CELL("filename";A1);1)) This is working ok.

I need all A1's to become clickable (hyperlink) to jump me to a specific cell in the first tab (MAIN) in the workbook, where I have a column of all tab names listed (C6:C280 & growing). This was the plan - 

=HYPERLINK(**xxxxxx-add_here-xxxxxxxxx**;RIGHT(CELL("filename";A1);LEN(CELL("filename";A1))-FIND("]";CELL("filename";A1);1)))

Tried adding something like (#'MAIN'!C)&MATCH(RIGHT(CELL("filename";A1);LEN(CELL("filename";A1))-FIND("]";CELL("filename";A1);1)));MAIN!C6:C280;0) , but there is a mistake somewhere.. Pls correct or suggest a better/simplier option. Thank you :)

This is a shared file, so no VBA/Macros/conditional formatting, etc allowed.

Solution 
  • =HYPERLINK("#Main!C"&MATCH((RIGHT(CELL("filename";A1);LEN(CELL("filename";A1))-FIND("]";CELL("filename";A1);1)))*1;Main!C:C;0);RIGHT(CELL("filename";A1);LEN(CELL("filename";A1))-FIND("]";CELL("filename";A1);1)))

    Ok, couple days fiddling and I came up with the solution myself. The above string works. Looks like the issue was with formatting as when the tab name is multiplied by 1 (thus making it a number) everything starts working. Go figure..