Link to an external workbook cells via a formula!

[pbr1985]

Hi all!

So I have a long list of workbooks for timekeeping, and their filenames are all in the same format (JobNumber:Name TimeKeeping)

For example I have 555.Jones TimeKeeping.xlsx and 556.Smith TimeKeeping.xlsx

I want to create a summary worksheet for all jobs, linking to different cells in the different workbooks, as below:

A B C D etc
Job no Name Worker Hours etc

Ideally I would enter A and B, and then C,D,etc would be able to concatenate the strings in these cells together with the other information needed to create the following link and return the value in that cell:

=('[555.Jones TimeKeeping.xlsx]Job Summary'!B5)

This way as more jobs are added I dont manually have to change the formula to the new workbook, I simple drag to continue the sequence!

The trouble is I know how to concatenate into a combined string, but not how to make it into a working formula.

Any help would be gratefully received!!

Thanks,

 

[BarryL]

where is the name of the workbooks kept? Are these open/closed workbooks?

 

[pbr1985]

The format of the file names is all the same, so in the summary sheet I will have columns with the job number in A, name in B, and then imported data in C, D, etc.

So from the data I put in A and B, the workbook name can be determined (Format is JobNumber . Name "Timekeeping" .xlsx) So i need a way of getting the text from columns A and B into the formula.

The workbooks will all be kept in the same folder, together with the summary sheet so relative references would work. They will be closed the majority of the times the summary sheet is opened.