+ Reply to Thread
Results 1 to 10 of 10

Hyperlinks to all sheets - output name modification

  1. #1
    Forum Contributor
    Join Date
    07-16-2009
    Location
    NA
    MS-Off Ver
    MS Excel 2010
    Posts
    237

    Hyperlinks to all sheets - output name modification

    Hi guys,

    The script below creates hyperlinks to cell A1 on every sheet in a workbook. Everything is running fine but I have a problem.

    Please Login or Register  to view this content.
    Instead of sheet name, I need to output the name of the cell it's referencing. For example, Sheet1 has in A1 "words". So, instead of displaying "sheet1" add "words" as hyperlink title


    Please Login or Register  to view this content.
    Instead of "back to index", I need the hyperlink to display the original context in that cell.
    Right now, If A1 from Sheet1 contains "words" and I run the script then it will be transformed in back to index. As before, instead of "back to index" disaplay "words"

    Below is the full script (got it from ozgrid.com). I'm sorry if I couldn't explain any better. If requested I'll add a sample. thanks guys.

    Please Login or Register  to view this content.
    Last edited by Zaeguzah; 05-12-2010 at 04:54 AM.

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,454

    Re: Hyperlinks to all sheets - output name modification

    Will this work for you?

    Please Login or Register  to view this content.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Forum Contributor
    Join Date
    07-16-2009
    Location
    NA
    MS-Off Ver
    MS Excel 2010
    Posts
    237

    Re: Hyperlinks to all sheets - output name modification

    Sadly, no. That's the first thing I tried and all it gives is:

    Runtime error '5':
    Invalid procedure call or argument.

    I must be doing something wrong I guess.

  4. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,454

    Re: Hyperlinks to all sheets - output name modification

    This works for me....

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    07-16-2009
    Location
    NA
    MS-Off Ver
    MS Excel 2010
    Posts
    237

    Re: Hyperlinks to all sheets - output name modification

    it works but it still replaces the contents in A1 with the sheet name. Tried it this way: i added a new shet, typed in A1 "somename" and then I went back to the index sheet and it replaced the name with sheet1.

  6. #6
    Forum Contributor
    Join Date
    07-16-2009
    Location
    NA
    MS-Off Ver
    MS Excel 2010
    Posts
    237

    Re: Hyperlinks to all sheets - output name modification

    Any updates on my problem?

  7. #7
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,454

    Re: Hyperlinks to all sheets - output name modification

    To clarify - in the index sheet you want the hyperlink to display the sheet name, and in all other sheets, the hyperlink to display the current value of that cell?

    If so, this should do it, if not, please try explaining again, and accept my apologies for not being too bright today.


    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    07-16-2009
    Location
    NA
    MS-Off Ver
    MS Excel 2010
    Posts
    237

    Re: Hyperlinks to all sheets - output name modification

    First, accept my apologies for not beeing clear. This is eactly what I want:

    • in the index sheet, I want each hyperlink to have the name of cell it's referencing (the value of A1 in this case) and not the sheet name.
    • in all other sheets instead of "back to index" I need it to display that cell's value as title.

    Let's say I have a sheet called "sheet1". Sheet1 cell A1 contains "DA". So, in the index sheet the hyperlink for sheet1 will have the name "DA". Sheet1 A1 that contains the hyperlink back to the index will now display "DA".

    Hope It's clearer now. And thanks man.

  9. #9
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,454

    Re: Hyperlinks to all sheets - output name modification

    Gotcha.

    Try this.

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    07-16-2009
    Location
    NA
    MS-Off Ver
    MS Excel 2010
    Posts
    237

    Re: Hyperlinks to all sheets - output name modification

    Thanks Sweep. I tested it yeasterday but couldn't find the time to post. Does exactly what I need it to do and works without a flaw. Thanks again .SOLVED!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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