Hyperlink Help

**mrstrent** · 02-25-2011, 12:33 PM

I have created a transmittal log. I have the first worksheet named "FTM Log", in file "FTM Log". In which is a spread sheet containing information within each FTM. The following worksheets are numbered 1, 2, 3, 4 ect.(Relating to the FTM Number).

So far, i have pieced together, via the internet, the following hyperlink to take me to the worksheet: (A column is FTM Numbers)
=IF(A4="","",HYPERLINK(MID(CELL("filename",A4),SEARCH("[",CELL("filename",A4),1),SEARCH("]",CELL("filename",A4),1)-SEARCH("[",CELL("filename",A4),1)+1)&"'"&A4&"'!A1","To FTM"))

Problem...
Now, at FTM no. 650+, is there a way i can make a hyperlink from the worksheet back to the row/column that the FTM No. is located (so i dont get lost/ scroll down 600+ columns):
So far i have:
=VLOOKUP(E9,'FTM Log'!A3:L20,11,FALSE) // gives me the =Row within FTM Log
=VLOOKUP(E9,'FTM Log'!A3:L20,11,FALSE) // gives me the =Column within FTM Log
N6= =ADDRESS(row,column) // gives me the hypothetical address from the last two formulas
=HYPERLINK("[FTM Log]FTM Log!"&N6,"To Log") // hypothetically should return me to where i want, but it does not.

Can anyone help? Anything would help (im self taught so please go easy...)

**DonkeyOte** · 02-25-2011, 01:04 PM

Your first HYPERLINK can be shortened somewhat

=HYPERLINK("#'"&$A4&"'!A1","To FTM")
copied down

Thereafter I'm not sure I follow 100% - I think you're saying you want to have hyperlinks on the individual sheets back to the Log tied to the appropriate cell (that containing the hyperlink to itself)

You mention 650 but your FTM Log table_array is only 18 rows ... you say the VLOOKUPs give you row & column but they are identical.

In theory if you assume per the original HYPERLINK that the digit (stored in E9 on individual sheet) is found in A3:A20 on Log sheet then:

=HYPERLINK("#INDEX('FTM Log'!$A$3:$A$20,MATCH($E9,'FTM Log'!$A$3:$A$20,0))","To Log")

**mrstrent** · 02-25-2011, 01:19 PM

Perfect! Thanks, you saved me alot of time!

For thoes following...

=HYPERLINK("#'"&$A4&"'!A1","To FTM")
worked like a charm...

and FTM - 634 formula is:
=HYPERLINK("#INDEX('FTM Log'!$A$3:$A$636,MATCH($E9,'FTM Log'!$A$3:$A$636,0))","To Log")
// E9 = worksheet name (FTM Number)

will return to A636 on FTM log.

Thank You!

**mrstrent** · 02-25-2011, 03:51 PM

Just a follow up... is there a function to find the last utilized cell in the range?
Say:
=HYPERLINK("#INDEX('FTM Log'!$A$3:$A$636,MATCH($E9,'FTM Log'!$A$3:$A$636,0))","To Log")

Question:
Is there a way to make the Range A3:A(last used) - so that the main log can continue to add to the range and never exceed the used range in this formula?

**DonkeyOte** · 02-28-2011, 09:13 AM

You can but in this instance I'd suggest simpler to build in sufficient excess in the precedent range.

If you want to use a Dynamic Range can you outline the data type in A3 onwards - is it always numeric for ex.
IMO you want to avoid a volatile OFFSET construct and revert to an INDEX/MATCH based approach