Dynamic Hyperlinks

Hi there,

I am producing an industry tracker in excel which has a summary page and several pages containing lists of news stories, patents and company lists. There are a different number of stories in my categories each month and so on the summary page I normally link just by manually adding in a cell reference to a hyperlink. For some reason this month, every time the file is closed and saved, the hyperlink address changes back to A1 of the summary page. It's been tried by my colleague too on her computer and the same things happen.

So I decided to look to solve two issues at once by moving to dynamic hyperlinks - and I tried a formula that I found online:

=HYPERLINK("#"&CELL("address",INDEX(Summary!B2:B32,MATCH(A3,Summary!B2:B33,0))),"Jump to the data cell")

I can reproduce the example given in a separate file, but when I try to apply it to my data it comes up with "N/A". This is what I'm typing:

=HYPERLINK("#"&CELL("address",INDEX(Table1[#All],MATCH(L8,Table1[#All],0))),"Jump to the data cell")

I put in a new column to my CorpDev page with a single letter (i.e. A,B,C,D,E) as the reference for each of the news items to match to, because it seemed as though the formula didn't work if you had more than one word in the cell. I then put the same column into my summary page.

When I evaluate the formula the N/A shows up as follows:

=HYPERLINK("#"&CELL("address",INDEX(Table1[#All],#N/A(L8,Table1[#All],#N/A))),"Jump to the data cell")

Any thoughts on why it is not working?

Many thanks

MKL

Thanks - that worked perfectly