Variable for Filename and sheet in INDEX-MATCH formula!

Hi,

I have created some code in Excel VBA, basically it enters a formula (Index, Match combo) in a cell in the active sheet and then it fills down to the last row.
The code I want to alter (that works in current state):
VBA Code:

Please Login or Register  to view this content.

However, I want to make it a bit more user friendly and dynamic.
How can I replace this part: '[sc_request1.xlsx]Page1'! with a variable?
In other words, workbook and sheet.
Ordinarily I can make this work and you can find examples everywhere but I guess it's the bangs, exclamation marks and single quotes that makes it complicated in this case.
Thanks!

For exasmple:

Please Login or Register  to view this content.