This formula relies on concatenation to assemble a valid location for the HYPERLINK function.
In cell D5, the link location argument is created like this:
"#"&B5&"!"&C5 // returns ""#Sheet1!A1""
which returns the string "#Sheet1!A1". The formula then resolves to:
Which returns a valid link.
The cell value in column C is entirely arbitrary and can be any cell you like. It could also be hardcoded into the formula as a string like this:
Note: The hash character (#) at the start of the sheet name is required. For more link syntax examples, see HYPERLINK.