I am trying to reference a cell in a closed workbooks

Question

I have a sheet where column B contains a list of Folders in R:\Jobs. The folder name is also the 'Job Name'.

I use VBA to create a formula in Column F that looks like this:

='R:\Jobs\Job Name[Job Name.xlsm]Bid'!$BP$4

The list is long enough now that the macro takes too long to run.

Is there a way to create a formula that does this?

Job Name is always the name of the folder and the xlsm filename. and "Bid" is always the sheet name.

Any help would be appreciated