Hyperlink Autofill

First post, thanks for this great tool, finally have come up with a question I can't find the answer by searching for on the site.

I'm trying to create a hyperlink from a cell in a workbook to another sheet in the same book that will display the contents of the linked cell. I've been able to get that far, but trying to autofill or copy the formula means I have to manually edit every single formula to move to the next cell down. Example of my goal/setup:

Sheet 1: Hyperlink that displays an individual's name (which is listed on Sheet 2)
=HYPERLINK("#'Sheet 2'!B4",'Sheet 2'!B4)
=HYPERLINK("#'Sheet 2'!B5",'Sheet 2'!B5)
=HYPERLINK("#'Sheet 2'!B6",'Sheet 2'!B6)

Sheet 2: List of names
Bob
Jim
Tom

The link location is what does not autofill correctly, the friendly name does automatically move down the column when dragged. Thanks in advance!

Shribbles

Hello Shribbles
Your first reference to Sheet2 is hard coded within double quotes ("") thus making it a fixed text value. Try the following:

Please Login or Register  to view this content.

Both Sheet2 references are now cell references and should move relatively.

Hope this helps.
DBY

Thanks for the quick reply!

I tried the formula you provided. When I hit "Enter" to complete it pops up requesting to update the file. Tried letting it choose the existing file, but it returns a "#NAME?" value.

Sorry I don't know why that would happen. I works fine for me on my test sheet. It returns links to Bob; Jim and Tom. These do have to be valid references in your workbook.

Originally Posted by DBY

Sorry I don't know why that would happen. I works fine for me on my test sheet. It returns links to Bob; Jim and Tom. These do have to be valid references in your workbook.

Those were examples, but I can send you the actual file if it would help. I can't give you all the information but the basics will still apply.

If you could upload an example file without any confidential information contained it might help myself or other forum members to see more clearly what you're trying to do.

Originally Posted by DBY

If you could upload an example file without any confidential information contained it might help myself or other forum members to see more clearly what you're trying to do.

Sure, absolutely. OER & NCOER Tracker Blank.xlsx

Not sure where you are wishing to place your hyperlinks and what are they linking to?

Hyperlink on first sheet (rating scheme) under name, link to second sheet (status comments). I'd like to have the name populate from sheet2 to sheet1 with a hyperlink to sheet2. Used to be just sheet1 but adding more functionality.

Right I see a little clearer, I think! In cell C5 downwards on the Rating Scheme Sheet try:

Please Login or Register  to view this content.

This shows the name and links to that cell on the Status Comments sheet. Is that correct?

Originally Posted by DBY

Right I see a little clearer, I think! In cell C5 downwards on the Rating Scheme Sheet try:

Please Login or Register  to view this content.

This shows the name and links to that cell on the Status Comments sheet. Is that correct?

That works, but only if the names don't get resorted (Z-A, or by Date for instance). If they get shuffled, the hyperlink still goes to the original cell, thus showing the wrong name.

Okay, try:

Please Login or Register  to view this content.

Originally Posted by DBY

Okay, try:

Please Login or Register  to view this content.

That did it! Thanks a bunch, I've been messing with this thing for a couple days now, glad you were able to help. So that's saying if cell contains a value, create hyperlink?

Happy it seems to work it is a little tricky with the sorting. The IF statement just avoids creating a blank hyperlink to a blank cell you could omit it if you wish.

DBY