Setting up a TOC / Index in Excel with absolute references

Hi, I am trying to set up an Index / Table of contents in an Excel File with fixed or absolute references. When I use the insert hyperlink function and fix my references using $ when I go back later they have disappeared. I have tried using contextures but while it works it does not have the option to rename it like you can with a hyperlink. I need the links to move when the cell moves when I add or delete rows etc.  The link I have used is =HYPERLINK("#'"&E2&"'!D2") I have attached the sample I have been working with. Any assistance would be greatly appreciated. Thanks Dot
Dot GlindemannAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neil FlemingConsultant and developerCommented:
If you want the links to move when you insert rows, then you need a relative reference in any case, I think?

The cleanest way to do this is probably to create "named references" for your cells.

In the attached version of your file I have named cell B7 in the "Controlled" sheet as "cManuals". You can easily do this in the box top left that normally contains the cell reference. See image below.

You can then create a hyperlink to the named reference, as I have done in the yellow cell G7  in your "index" sheet.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Neil FlemingConsultant and developerCommented:
Hmm. I suspect I only answered part of the question.

The further step to use a named range using the =HYPERLINK() function would be as follows.

=HYPERLINK("#Controlled!" & ADDRESS(ROW(cManuals),COLUMN(cManuals)),"Manuals")

Open in new window

The named range will continue to reference "Manuals" no matter what you insert above or below it, or to left and right.
Rob HensonFinance AnalystCommented:
I think what you are trying to do is:

For example: Where you have "MGT - Management" on your "Index" sheet you want to hyperlink to the row that contains the same on the "Controlled" sheet.

This is currently on row 8 but if items get added or removed from "Controlled" sheet the row that holds "MGT - Management" will move but you want the hyperlink to still go to the correct row.

Is that correct?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Ejgil HedegaardCommented:
You can point directly to the cell in column B on sheet Controlled with the Hyperlink function.
Like this
Then you will get the text in Controlled!B7, in cell INDEX!A2, and the hyperlink will adjust when rows are deleted or inserted.
If you want to go to column A, instead of B, use
Are you doing this with the Go language?
Dot GlindemannAuthor Commented:
Thank You all for your comments and assistance with this problem. My spreadsheet is now working they way that I want it to and you have saved me a lot of frustration. Have a great day. Thanks Dot
Neil FlemingConsultant and developerCommented:
Glad it was useful, Dot.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.