+ Reply to Thread
Results 1 to 35 of 35

hyperlink to another sheet based on specific text

  1. #1
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    hyperlink to another sheet based on specific text

    Hello, I have two worksheets, my intention is from the table in 'sheet2!' make aditional hyperlinks that could take me to the specific cell that has same TEXT. Note: the cells in 'sheet1!' having the same TEXT is not in column, but in row, so I need hyperlink formula that could locate me directly to that cell.

    I attach an example what I want to do


    question.xlsx

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: hyperlink to another sheet based on specific text

    See if this is what you want?

    =HYPERLINK("#"&"'sheet1'!"&ADDRESS(1,MATCH(B2,Sheet1!$A$1:$G$1,0)),"Age "&B2)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    It looks like it should work. but whenever i write formula, the error occurs and says "the formula you typed contains error". what im doing wrong?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: hyperlink to another sheet based on specific text

    Can you upload a sample workbook?

  5. #5
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    question.xlsx
    image error.jpg

    I'm new in excel, I dont really know how VBA works, so i guess the error occured because i didn't folow corectly your first point
    "Use code tags for VBA.
    Please Login or Register  to view this content.
    (or use the # button)"
    Last edited by purlo; 10-04-2014 at 06:12 AM.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: hyperlink to another sheet based on specific text

    That part is for if you are posting VBA code, you are not so dont worry about it

    Not sure exactly what went wrong in your file, but I pressed F2 (edit), then enter and it worked fine

  7. #7
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    Could you attach a file?

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: hyperlink to another sheet based on specific text

    All I did was open the file you uploaded, selected that cell (the cursor was already there) and then pressed F2 and enter

  9. #9
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    well it still show me the error i uploaded in an image. i dont understand the problem either. i use excel 2010
    after i push "OK" in the error table it higlights this part of the formula: 1,MATCH
    Last edited by purlo; 10-05-2014 at 01:07 PM.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: hyperlink to another sheet based on specific text

    OK I will take another look for you

  11. #11
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    ok thanks, maybe you could upload file wiht working formula so i can check if it works for me, thanx

  12. #12
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: hyperlink to another sheet based on specific text

    You entered the formula with a space in front of the = sign. With the space removed, the formula works just fine.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  13. #13
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    erm no, if there were a space program wouldnt recognize a formula and write it as a text. in my case when i push enter the table occurs with error i posted erlear. i tried it on different PC still didnt work

  14. #14
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: hyperlink to another sheet based on specific text

    Strange. It worked in your posted example.

  15. #15
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    error.jpg
    Attachment 350200
    it says "The formula you typed contains an error"


    u can see in this picture. maybe i need to update excel or what? i use 2010
    Last edited by purlo; 10-07-2014 at 01:00 PM.

  16. #16
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: hyperlink to another sheet based on specific text

    I am unable to view your pictures.

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: hyperlink to another sheet based on specific text

    I also cannot see your pic, but here is your file - I opened it, clicked on C2, pressed F2 and enter
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    ok ur file works...
    thanks for helping me out!
    Last edited by purlo; 10-07-2014 at 02:28 PM.

  19. #19
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: hyperlink to another sheet based on specific text

    you are welcome, and thanks for the feedback

  20. #20
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    one more question regarding this. is it posible to change "Age"&B2 into "Age"& (and the cell that is in the same column as the cell to which hyperlink is directed) : question 3.xlsx

  21. #21
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: hyperlink to another sheet based on specific text

    You can make it "say" whatever you want to, by changing the last part of that formula...
    =HYPERLINK("#"&"'sheet1'!"&ADDRESS(1,MATCH(A2,Sheet1!$A$1:$G$1,0)),"Age "&Sheet1!B2)

  22. #22
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    Yes, but if I copy it down it becomes b3 while my text is in let's say b5

  23. #23
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: hyperlink to another sheet based on specific text

    like what? give a few examples please

  24. #24
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    you can see in my file. if i copie down this &Sheet1!B2 it becomes &Sheet1!B3 and so on. I need it to become &Sheet1!D1 and so on Automaticlly according to column there this hyperlink formula founds a number.
    if in B1 and B2 is "17" and "john" hyperlink founds and directs me to 17 and it show &Sheet1!B2 which is "john". but when i copy it down, formula ofunds d1 "19" but &Sheet1!B2 argument becomes &Sheet1!B3, i need it to become d2 "mike"

  25. #25
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: hyperlink to another sheet based on specific text

    This is becoming, at least to me, a bit confusing. Is this what you are wanting to achieve?

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    Age
    2
    John
    17
    John Age 17 =HYPERLINK("#"&"'sheet1'!"&ADDRESS(1,MATCH(B2,Sheet1!$A$1:$G$1,0)),A2&" Age "&B2)
    3
    Luke
    20
    Luke Age 20
    4
    Tom
    19
    Tom Age 19
    5

  26. #26
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    Thanks
    One more question. Is it possible for hyperlink to find a certain text in entire sheet, then neither column or row is specified?
    Last edited by purlo; 10-17-2014 at 09:49 AM.

  27. #27
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: hyperlink to another sheet based on specific text

    Hyperlinks go to where they are pointed. I haven't seen one that does a search.

  28. #28
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    lets say i need C5 cells text to be found in another sheet and make a hyperlink. I have this formula =HYPERLINK("#"&"'sheet1'!"&ADDRESS(13;MATCH(C5;sheet!$A$13:$ABO$13;0));"go to ") but i need it to search not in 13 row but in all rows and ofcourse columns

  29. #29
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: hyperlink to another sheet based on specific text

    This formula finds the address on Sheet2 of the value entered in sheet1!C1. Clicking on the hyperlink will then make that address on Sheet1 active. The yellow area on Sheet2 is the range being searched. Change the references in the Address part of the formula to change the range.

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

  30. #30
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    ok it works, i just had to change sheet1 to sheet2, thanks!
    Last edited by purlo; 10-18-2014 at 10:15 AM.

  31. #31
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: hyperlink to another sheet based on specific text

    I don't know what worked for you but thank you for giving feedback on the problem.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  32. #32
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    Hello, I need simmilar hyperlink formula that could find text in another sheet's column
    =HYPERLINK("#"&"'Timespan'!"&ADDRESS(13;MATCH(C4;Timespan!$B$13:$AGB$13;0));" "&C4) i need to change this formula so that as i mentioned it could find text in column, not a row.
    and one more question. I have this formula
    =HYPERLINK("#"&"'map'!"&ADDRESS(SUMPRODUCT(('map'!A1:Z999=O4)*ROW(A1:Z999));SUMPRODUCT(('map'!A1:Z999=O4)*COLUMN(A1:Z999)));" "&O4) and it finds a text based on it value anywhere in another sheet, but the problem is, when i copy down this formula the array is always changing, for example:

    =HYPERLINK("#"&"'map'!"&ADDRESS(SUMPRODUCT(('map'!A1:Z999=O4)*ROW(A1:Z999));SUMPRODUCT(('map'!A1:Z999=O4)*COLUMN(A1:Z999)));" "&O4)
    =HYPERLINK("#"&"'map'!"&ADDRESS(SUMPRODUCT(('map'!A3:Z1001=O6)*ROW(A3:Z1001));SUMPRODUCT(('map'!A3:Z1001=O6)*COLUMN(A3:Z1001)));" "&O6)
    what do i have to do so that the array does not change then i copy down the formula?

  33. #33
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: hyperlink to another sheet based on specific text

    Purlo, Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  34. #34
    Registered User
    Join Date
    02-27-2014
    Location
    Lithuania, Klaipeda
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: hyperlink to another sheet based on specific text

    well its my theard, but i created it few months ago... anyways this question is closly connected with what was dissucesed here
    so should i create new thread?

  35. #35
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: hyperlink to another sheet based on specific text

    I advise to create a new thread with an accurate description of what you want, as of now. Include a workbook that demonstrates what you want.

+ 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. [SOLVED] Copy rows with specific text in specific column into specific sheet
    By Valemaar in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-22-2014, 03:23 PM
  2. [SOLVED] VBA to pull data from worksheet to a new sheet based on specific text cell value
    By TalResha in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-13-2013, 02:11 AM
  3. [SOLVED] Hyperlink to specific text in another worksheet (II)
    By benishiryo in forum Excel General
    Replies: 3
    Last Post: 05-22-2012, 03:08 AM
  4. Hyperlink to specific text in another worksheet
    By rvdsabu4life in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-18-2008, 10:54 PM
  5. [SOLVED] Hyperlink to Specific Sheet
    By mpeplow in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-16-2007, 05:46 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