Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    North Vancouver, Br. Columbia, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlink: Problem with Spaces (Excel 2000)

    I'm creating an index page for a workbook with a large number of sheets. I've got a formula which hyperlinks to any sheet, even if the name of the sheet is changed. For instance, clicking on the cell which contains the following formula will hyperlink to cell A1 of Sheet2: =HYPERLINK(CELL("address",Sheet2!A1)) [The workbook must be saved for this function to work]

    Problem: The hyperlink stops working if either the target sheet name or saved file name includes one or more space characters. Can anyone see a way to modify the formula so that the hyperlink keeps working, irrespective of whether spaces are in the target sheet-name or target file-name? I've tinkered unsuccessfully with replacing spaces in the string with , but with no success.

    Thanks in advance,
    Archie

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink: Problem with Spaces (Excel 2000)

    Try:

    <pre>=HYPERLINK(CELL("address",'Space Sheet2'!A1))
    </pre>

    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    North Vancouver, Br. Columbia, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink: Problem with Spaces (Excel 2000)

    I tried it, but I don't think your solution works. My formula automatically becomes your formula if "Sheet2" is renamed "Space Sheet2", but the space in the hyperlink string seems to cause the failure in the hyperlink.

    Regards,
    Archie

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink: Problem with Spaces (Excel 2000)

    OOPS! That looks like a bug in the HYPERLINK function. I was not able to find a way around the problem. However, I was able to use Insert Hyperlink from the Insert menu to create a link that would work. Can you use that?
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Jan 2001
    Location
    North Vancouver, Br. Columbia, Canada
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink: Problem with Spaces (Excel 2000)

    I'm using this function for a document that will be used as a template by many different users, and I need the hyperlink to survive a renaming of a sheet by any user, so the Insert Hyperlink approach doesn't work for me.

    Thanks,
    Archie

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink: Problem with Spaces (Excel 2000)

    Sorry, then I don't have any other suggestions.
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink: Problem with Spaces (Excel 2000)

    Legare,

    Could a macro be written that would check Tab names and strip any spaces from the Tab name?

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink: Problem with Spaces (Excel 2000)

    That would be possible, but would users like it? Moreover, what if a user has "Sheet1" and "Sheet 1"?

  9. #9
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink: Problem with Spaces (Excel 2000)

    Hans,

    Agreed. It would may displease some of the users. The macro could be written to check against all tab names to avoid dupes, or replace spaces with a different character.
    It was just a thought.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  10. #10
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Hyperlink: Problem with Spaces (Excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> reimer

    My Two cents worth <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

    Your approch is very valid, but I would add that instead of looking at the worksheet name, which could be anything, the VBA code should look at the worksheet object name.

    I think it is harder for a casual user to change the worksheet object name, well first they have to know where it is, and that makes it a better choice than the all visible worksheet name that you see in the worksheet tabs.

    The VBA code should look at the worksheet object name, and compose the hyperlink for that worksheet and set the name of the worksheet from the worksheet tab for display.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  11. #11
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Hyperlink: Problem with Spaces (Excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Archie

    Please see my reply to reimer in this thread.

    The VBA code should be looking at the Worksheet Object Name, in the VBE, vs the one in the tabs.

    The former is harder to change by the user, and you can control it better via VBA code as well.

    If a user adds a worksheet, this event will trigger the event handler and rename the object the way you want to.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink: Problem with Spaces (Excel 2000)

    But Archie wants to create formulas with the HYPERLINK worksheet function. HYPERLINK does not accept the code name of a worksheet, it must work with the "visible" name of the worksheet.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Hyperlink: Problem with Spaces (Excel 2000)

    You could try a macro approach instead of using hyperlinks.

    On each sheet you need to "goto" with a link, create a named range onto the sheet using a descriptive name (rSheet1, rDataSheet, etc)

    Then create a macro with the parameters of the range name of the sheet and the cell address:

    <pre>Sub GotoCell(sRngName As String, sAddress As String)
    Range(sRngName).Parent.Select
    Range(sAddress).Select
    End Sub</pre>


    Where you would put a hyperlink, add an object (a FORMS toolbar label with no text is good) and assign a macro to it. the macro would be one line (eg):

    <pre>Sub GotoSht1A100()
    GotoCell "rSheet1", "A100"
    End Sub</pre>


    Of course you could have a macro decide based on some conditions in the workbook or a range, what the rangename and the address would be.

    You could create multiple macros (instead of hyperlinks), or just a few that read info from the spreadsheet (whatever is required). The parent of named range will not be affected by the sheet name changing as the "refers to" will be updated. You could make the names "hidden" for an extra layer of protection from changing.

    Steve

Posting Permissions

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