Excel:=HyperLink() (or how to integrate your spreadsheet with Moovee Picker)

I initially put this together for Liem in an email so he could link the Moovee Picker page to his fancy spreadsheet.

The Basics:

1. Your box office data is in a single column.  (It could be in rows, but for this example the movie information fills down.)
2. Your box office data does not move, because it will be referenced by other cells.
3. You may create another sheet in your workbook to hide all the messy “calculations”. (If you do this then you’re familiar how to reference other sheets from your base sheet.)

Your Data:

The first sheet will contain your movie names (column A) and box office values (column B).  You may have other random columns inserted, but for this example we only have three data columns.  The BUX column isn’t really used, but mostly to show that the movie data is sorted properly and matches the sorting within Moovee Picker.

Note that cell A19 is selected and you can see the referenced HYPERLINK() function =HYPERLINK(‘MP Link’!A21, “Click for picks…”) where the link is referenced on the MP Link sheet and “Click for picks…” is the friendly name.

Data Transformation:

Since the hyperlink needs to be a long string of characters then this needs to be built using the =CONCAT() function which you can see is being used in cell C3.  The movie name column is populated by using the sheet reference =Data!A2 from Black Panther [Saturday] and filled down to The Shape of Water.  The same goes with the box office column; =Data!B2 and filled down.

Since cell C3 is referencing the cell above it C2, cell C2 needs to be populated with cell B2 by just entering =B2 in cell C2.  Cell C3 is highlighted because this is where the work happens.  This cell contains the function =CONCAT(C2, “,”, B3) which references the cell above and concatenates that with a comma and the cell to the left B3 and the result is “27900000,20600000”.  When C3 is filled down to cell C16 all of the box office values are concatenated together in the correct order. That was the hard part.

Piecing It All Together:

The function in cell A21 contains =CONCAT(A18,A20, “&”, A19) which concatenates the root URL (A18), the box office values (A20), a separator between the lists (&), and the weight list designating to only use the custom box office values (A19).  Now cell A21 is ready to be referenced on the previous “Data” (Data!A19) sheet in the first figure.

“I have Excel, Just give me the workbook and I’ll sort it out.”

What’s Moovee Picker?

I hope this helps, I can always be contacted through Twitter or my email.

Excel HYPERLINK() reference from Microsoft