I am trying to get totals from various closed spreadsheets and collate on to a separate spreadsheet. I am using the vlookup function below:

=VLOOKUP(A1,'C:\Users\Colin\Documents\Book1.xlsx]Sheet1'!$B$1:$C$19,2)

So cell A1 contains the word Total, I want to look in cell B1:C19 for this word in several external closed spreadsheets.

I want to replace the Book1.xlsx with a variable.

Can anyone assist?

Cheers

Colin