File reference from cell

[NikToo]

I'm trying to do something that looks up a value in a file based on a source directory as defined in a cell. So I have in cell M5 in this case a directory, like "C:\Users\111222333\Documents\Source", and in another cell I want to display a value in a cell in the file C:\Users\111222333\Documents\Source\data.xls.

I can write it out in full, adding Sheet!A1 for example. The problem is that I want to be able to change the source directory in M5. What would the syntax be for that?

Thanks

 

[AliGW]

You could try something like this:

=INDIRECT(M5&"\data.xls")

 

[NikToo]

You could try something like this:

=INDIRECT(M5&"\data.xls")

Thanks. It should theoretically work, but I'm getting an error. My file name has spaces in it, so how would I reference it all? Like this: "\My data file.xlsb!Sheet1!A1"?

 

[AliGW]

Yes, exactly as it is. What is the error?