+ Reply to Thread
Results 1 to 7 of 7

Problem with empty space in Hyperlink function

  1. #1
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague
    MS-Off Ver
    Excel 2013
    Posts
    159

    Problem with empty space in Hyperlink function

    Hi all,

    I've create formula which will show the name of the sheets in a file, and will create hyperlink to them. If the sheet name is without empty space (e.g.: "Sheet1", "Sheet2"), it works perfectly. But if I have empty space in the sheet name (e.g. "Sheet 2", "Sheet 4"), the hyperlink function does not work.

    Can anyone help me with this.

    Thank you in advance.

    Igor

    Hyperlink formula.xlsb

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Problem with empty space in Hyperlink function

    Space can not allow in hyperlink you can use "_" or if you have two name sheet then name like Sheet3Work you can use. This rule is also applicable in name manager.

    Hope now you have understood.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Problem with empty space in Hyperlink function

    Hi,

    If you evaluate the Formula, you get a space in front of Sheet 4.

    And yes, what shukla.ankur281190 might also be true
    Last edited by NeedForExcel; 09-22-2015 at 07:57 AM.
    Cheers!
    Deep Dave

  4. #4
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Problem with empty space in Hyperlink function

    @shukla.ankur281190

    Space can not allow in hyperlink
    But, I am not sure that is the case really..

  5. #5
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Problem with empty space in Hyperlink function

    Yes it will happen i guess, you can see that the outcome sheet names are ok as they are but still it can not load properly. When we add "_" instead on space it works perfectly.

    Seriously I'm also not sure that hyperlink will work with space or not.

  6. #6
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: Problem with empty space in Hyperlink function

    Try this in B2 and Drag Down

    =IFERROR(HYPERLINK("#'"&RIGHT(INDEX(Sheetlist,ROW()),LEN(INDEX(Sheetlist,ROW()))-1)&"'!A1",INDEX(Sheetlist,ROW())),"")
    Attached Files Attached Files
    Last edited by NeedForExcel; 09-22-2015 at 08:26 AM.

  7. #7
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Problem with empty space in Hyperlink function

    Great work Today I have learned two things from you .... Thanks

+ 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. Hyperlink problem if space in Sheet Name
    By mubashir aziz in forum Excel General
    Replies: 13
    Last Post: 10-21-2021, 10:35 AM
  2. [SOLVED] How to remove empty space, unable to make pvot, because pvot calculate empty space
    By vengatvj in forum Excel Charting & Pivots
    Replies: 20
    Last Post: 10-29-2013, 12:43 PM
  3. Mid function and first empty space
    By twckfa16 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-21-2013, 07:30 PM
  4. 0 different from empty space
    By gexxy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-24-2013, 01:46 PM
  5. [SOLVED] Hyperlink function that goes to first empty or blank cell in a sheet fails.
    By Aussie Mat in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-05-2012, 09:58 PM
  6. [SOLVED] Hyperlink function problem
    By otspbob in forum Excel General
    Replies: 1
    Last Post: 03-29-2006, 01:55 PM
  7. =HYPERLINK function and Excel 2003 problem.
    By [email protected] in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-10-2006, 11:20 AM
  8. [SOLVED] Hyperlink Function Problem
    By Peter Ramm in forum Excel General
    Replies: 5
    Last Post: 10-08-2005, 06:05 AM

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