+ Reply to Thread
Results 1 to 3 of 3

Fill static hyperlink with cell text from different worksheet

  1. #1
    Registered User
    Join Date
    06-11-2011
    Location
    Carrboro, NC
    MS-Off Ver
    Excel 2010
    Posts
    32

    Exclamation Fill static hyperlink with cell text from different worksheet

    I'm trying to automatically generate links to RottenTomatoes based on movie title text in a separate worksheet. Because reviews on RottenTomatoes are found in the form of http://www.rottentomatoes.com/m/movietitle, I figured I could simply copy the string from a cell to fill in the "movietitle" portion in each link.

    In terms of specifics, sheet 1 includes a list of movie titles (e.g., "Silences of the Lambs"), and I'd like for sheet 2 to take the movie titles from sheet 1 and create links to RottenTomatoes in the form of "http://www.rottentomatoes.com/m/MOVIE TITLE TEXT FROM CELLS IN OTHER WORKSHEET". In the example provided, the link would go to http://www.rottentomatoes.com/m/Silence of the Lambs.

    I know that to have a string fill a portion of a link within the same worksheet, you type "&A1&" (if it's A1), but how do you do the same thing for text from a different worksheet? I've tried using "&Sheet1!A1&" and "Sheet1!&A1&", but neither works. Instead, the link just ends up going to "http://www.rottentomatoes.com/m/Sheet1!A5".

    Any help would be greatly appreciated. Thanks!
    Last edited by newnoise; 09-26-2011 at 11:01 AM. Reason: clarify

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Fill static hyperlink with cell text from different worksheet

    Anything in double quotes is a text string. Don't put cell references in double quotes.

    ="http://www.rottentomatoes.com/m/" & Sheet1!A5

    ..or best of all:

    =HYPERLINK("http://www.rottentomatoes.com/m/" & SUBSTITUTE(Sheet1!A5, " ", "_"), Sheet1!A5)


    The last one would let you list the A5 cell as Gone With The Wind and it would convert the spaces for you.
    Last edited by JBeaucaire; 09-26-2011 at 11:05 AM. Reason: corrected the hyperlink version
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    06-11-2011
    Location
    Carrboro, NC
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Fill static hyperlink with cell text from different worksheet

    Perfect! Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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