+ Reply to Thread
Results 1 to 11 of 11

Hyperlink Cell name / sheet name - to change automatically

  1. #1
    Registered User
    Join Date
    07-26-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    36

    Hyperlink Cell name / sheet name - to change automatically

    Dear All

    I have a list of team members on Summary sheet and they are all listed from columns G5 to G31. They are all been hyperlinked to their own individual sheets showing their data. Issue is I have a few employees listed as Employee A, B, d etc and we change them to actual name when we need them. So only option is to change the sheet name and then change exactly in Column G cells. So for example if a new employee Dolly starts, if in G27, its employee Z, then i need to change Sheet employee Z to Dolly, and then change G27 to Dolly as well. I will still get error "reference invalid", but ateast the hyper link works. Is there a way to automate it, ie change the cell value and it automatically changes the hyperlinked sheet name as well?

    Regards and Thanks.

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Hyperlink Cell name / sheet name - to change automatically

    do you require VBA solution or would a formula suffice?

    use INDIRECT formula to generate the hyperlink based on G cells
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    07-26-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Hyperlink Cell name / sheet name - to change automatically

    Thanks, How to do it with VBA? Kindly help

  4. #4
    Registered User
    Join Date
    07-26-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Hyperlink Cell name / sheet name - to change automatically

    Thanks, How to do it with VBA? Kindly help

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    61,986

    Re: Hyperlink Cell name / sheet name - to change automatically

    Be patient, please!
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Hyperlink Cell name / sheet name - to change automatically

    well you either do it thru a worksheet_change event or have it run manually when you feel like updating it
    either instruct it to create the same said formula using VBA syntax to determine the sheet reference via cell or directly hard code an anchored hyperlink

    would be easier to explain how this would work if you provide a sample file

    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.

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Hyperlink Cell name / sheet name - to change automatically

    I've setup dummy sheet with your range

    press the button to update hyperlinks
    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-26-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Hyperlink Cell name / sheet name - to change automatically

    Apologies my friend, what happened is my little daughter unplugged the Ethernet cable and the connection was coming back on. So had to press enter a few times to get the message across here. Sorry didnt mean to press the panic button; )

  9. #9
    Registered User
    Join Date
    07-26-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Hyperlink Cell name / sheet name - to change automatically

    Apologies humdingaling, I was on bereavement leave off late and couldn't respond in time.

    I've attached a sample of what I m trying to achieve. As on summary sheet. If i change Cell F8,F9 or F10 to a particular Name, lets say Andy, the corresponding sheet name should also automatically change to Andy. At present what i am doing is changing sheet name and then copy paste the same into the corresponding cell on summary sheet.

    Hope I've explained well. Please advise when you get a chance.

    Thanks and Kind regards
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,637

    Re: Hyperlink Cell name / sheet name - to change automatically

    no need to be sorry, its not like losing sleep awaiting for replies from this forum


    the code is pretty much the same
    added in extra ' around the sheet name

    the thing is if the sheet is hidden it will not work
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-26-2013
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Hyperlink Cell name / sheet name - to change automatically

    Thanks so much, apologies for the massive delay

+ 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. Change hyperlink automatically according to the cell
    By anavasis in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-24-2017, 02:45 PM
  2. [SOLVED] Hyperlink Formula Cell reference change Automatically
    By disepyon in forum Excel General
    Replies: 10
    Last Post: 07-23-2017, 05:58 AM
  3. How to automatically copy cell with hyperlink from one sheet to the other
    By shalhevet in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-14-2013, 05:48 PM
  4. Automatically Change formatting of Cell in Another Sheet
    By BazzaBoy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-30-2012, 09:39 PM
  5. automatically change cell value based on the value in other sheet.
    By star.dust9212 in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 11-15-2011, 06:21 AM
  6. Replies: 3
    Last Post: 06-10-2011, 09:41 AM
  7. Automatically Change Sheet Tab Name With Date in Cell
    By chrisneu in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-31-2011, 11:34 PM

Tags for this Thread

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