Thread: 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!

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.