+ Reply to Thread
Results 1 to 6 of 6

Hyperlinks - change sheet but not cell reference for multiple hyperlinks at once

  1. #1
    Registered User
    Join Date
    07-18-2018
    Location
    Huddersfield, England
    MS-Off Ver
    2016
    Posts
    3

    Hyperlinks - change sheet but not cell reference for multiple hyperlinks at once

    Hello. Hoping someone might be able to help and potentially save me lots of time!

    I have a document that has 8 sheets, 4 with different data tables in them and 4 contents pages. I've set up hyperlinks on one contents sheet to take people to the relevant tables.

    The other three contents sheets need to include exactly the same cell references but the sheet number needs to be changed (the tables are the same size doing down the rows, but are different across the columns).

    I've tried selecting all the relevant cells and changing the hyperlinks together but this doesn't work. I've tried setting up a macro (unsuccessfully).

    Does anyone have any idea whether this can be done, other than editing each hyperlink individually to select a different sheet? That would take a VERY long time.

    Hope I've explained that clearly. Thanks in advance!

    Matt

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Hyperlinks - change sheet but not cell reference for multiple hyperlinks at once

    Can you show your unsuccessful macro and provide an example workbook?

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    07-18-2018
    Location
    Huddersfield, England
    MS-Off Ver
    2016
    Posts
    3

    Re: Hyperlinks - change sheet but not cell reference for multiple hyperlinks at once

    Thanks for getting back so quickly.

    I've uploaded an example spreadsheet. I'll try to explain...

    The sheet 'CB1 Index' has hyperlinks set up to the relevant tables on the 'CB1' sheet.

    I want to be able to update the hyperlinks on 'CB3 Index' to correspond with the 'CB3' sheet, but they have been copied from 'CB1 Index' so currently link back to the 'CB1' sheet.

    Is there a way to update the sheet reference, but keep the cell reference the same (as this will not change)? Obviously in this example it wouldn't take so long, but the document I'm working in has a much longer list and there are multiple sheets.

    Thanks in advance!

    Matt
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Hyperlinks - change sheet but not cell reference for multiple hyperlinks at once

    Here's a solution that doesnt require a macro.

    Use the Hyperlinks formula to make your hyperlinks and have one cell on the sheet (cell B1 in this example) to define the destination sheet name.

    So on sheet CB1 Index, put the sheet name of the destination sheet CB1 in cell B1. Remove all the hyperlinks on the sheet and replace them with a formula like this...

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Use that syntax for each hyperlink.

    Now when you copy sheet CB1 Index, all you have to do is change the destination sheet name in cell B1 and all the hyperlinks will change.

  5. #5
    Registered User
    Join Date
    07-18-2018
    Location
    Huddersfield, England
    MS-Off Ver
    2016
    Posts
    3

    Re: Hyperlinks - change sheet but not cell reference for multiple hyperlinks at once

    Hi AlphaFrog,

    I've just given this a go and it's working. You're a genius. Thank you very much. I'll make sure I set it up like this again in the future.

    Matt

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Hyperlinks - change sheet but not cell reference for multiple hyperlinks at once

    You're welcome. Thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Cell Reference don't change on sorting hyperlinks!!
    By sid_melb in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-06-2015, 08:27 PM
  2. Replies: 8
    Last Post: 08-09-2013, 06:10 AM
  3. Changing sheet reference on multiple hyperlinks
    By Dobo in forum Excel General
    Replies: 2
    Last Post: 07-18-2013, 09:04 PM
  4. Find Hyperlinks, Copy Hyperlinks to alternative sheet, print all hyperlinks
    By matrixpom in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2013, 05:13 PM
  5. VBA script with multiple input box's to change all hyperlinks in sheet
    By Slummy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-10-2013, 08:50 AM
  6. [SOLVED] Excel Hyperlinks- cell content v. hyperlinks
    By herpetafauna in forum Excel General
    Replies: 2
    Last Post: 05-22-2006, 11:45 PM
  7. [SOLVED] Can i change multiple hyperlinks at once
    By Magser in forum Excel General
    Replies: 3
    Last Post: 10-25-2005, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1