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!
Bookmarks