Cell Conditional Workbook Referencing

Can I type in a word or number in one excel file and have the spreadsheet look up another excel file with that word or number at the beginning or end of the file name and have it give me various values spread out through that spreadsheet?

For example: I type “5” into “master_spreadsheet.xlsx” (into cell A1 Sheet 1). “master_spreadsheet.xlsx” B1 Sheet 1 contains a formula that gives me the value in cell C19 Sheet 1 in the file “test 5.xlsx”. “master_spreadsheet.xlsx” C1 Sheet 1 would contain a formula that gives me the value in cell J46 Sheet 2 in the file “test 5.xlsx”. “master_spreadsheet.xlsx” D1 Sheet 1 would contain a formula that gives me the value in cell D22 Sheet 2 in the file “test 5.xlsx”.

I would then type “6” into “master_spreadsheet.xlsx” (into cell A2 Sheet 1). Cells B2, C2, and D2 in Sheet 1 of “master_spreadsheet.xlsx” should then fill in automatically (just like B1, C1, and D1 did). They should have the values of C19 Sheet 1, J46 Sheet 2, and D22 Sheet 2 respectfully of the file “test 6.xlsx”

A “22” in A3 Sheet 1 of “master_spreadsheet.xlsx” should make cells B3, C3, and D3 fill in automatically with C19 Sheet 1, J46 Sheet 2, and D22 Sheet 2 of the file “test 22.xlsx”

Basically, is there some way I can get this general idea to work:
='C:\...\...\...\[Test A1.xlsx]'Sheet1:$C$19
This exact method doesn't work, it comes up with an error. If there was some formatting trick I could use to get it to work that would be great.

I am using Excel 2007, but if it works for 2003 as well that would be great.

Thanks for all the help