Referencing Cells to another worksheet

[KlayColgrove]

I am trying to reference cells from another worksheet, but I have a ton of work sheets I'm trying to reference. I have the formula to reference the cells, but I'm looking for a way to drag down and be able to reference from corresponding list.

Here is the formula:
='P:\Tools\AUSAH\Pickup Tool.xlsm'!DaysLoaded

I would like to change the "AUSAH" to another name in the list by dragging it down.

 

[DanteAmor]

Something like this:

=INDIRECT("'P:\Tools" & E2 & "\Pickup Tool.xlsm'!DaysLoaded")

 

[DRSteele]

Function INDIRECT will only work when the file to which it refers is open. It will report an error for closed files.