This is the best I can get. Incase anyone searches and needs the best answer for this type of data pull. I had to break it down into sections; these work computers just can't handle this type of load. Max data pull is around 800 lines and takes around a minute to pull all the formula(s) and data. Thanks to the people below with thier help.

```
Sub Update()

Dim ScreenUpdateState As Boolean
Dim StatusBarShow As Boolean
Dim CalcState As Long
Dim EventState As Boolean

Dim ws As Worksheet
Dim location_string As String
Dim count As Integer

'Save the current state of Excel settings
ScreenUpdateState = Application.ScreenUpdating
StatusBarShow = Application.DisplayStatusBar
CalcState = Application.Calculation
EventState = Application.EnableEvents

'Change Excel to faster procedure settings
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

Set ws = ThisWorkbook.Sheets("%")
location_string = Sheets("Driver(s)").Cells(5, "G").Text

For count = 7 To 139
Cells(count, "F").Formula = "=IFERROR((VLOOKUP($C" & count    & ",'S:\xxxx\xxxxx\xxxxxx\xxxxx\xxxxxxxxxx\[xxxxxxxxxxxxxxxxxxxxxxxxxx.xlsx]" + location_string + "'!$A:$K,11,FALSE)),"" - "")"
Next count

'Restore Excel settings to original state
Application.ScreenUpdating = ScreenUpdateState
Application.DisplayStatusBar = StatusBarShow
Application.Calculation = CalcState
Application.EnableEvents = EventState

MsgBox ("Update Complete")
End Sub

```

Good luck!

* Ross

***Orignal thread:***

> 
> Ok I have this now and it works. It however is to slow to be used as
>  this one code only runs maybe 1/16th of the required calculations and
>  takes a few minutes to complete. Anyone know a way to speed up the
>  process?
> 
> 
> 
> ```
> Sub Test()
> Dim ws As Worksheet
> Dim location_string As String
> Dim count As Integer
> 
> Set ws = ThisWorkbook.Sheets("%")
> location_string = ws.Cells(2, "E").Text
> 
> count = 7
> 
> While count < 138
> Cells(count, "F").Formula = "=IFERROR((VLOOKUP($C" & count & ",
> 'S:\xxxx\xxxx\xxxx\xxxx\xxxxx\[xxxxxx.xlsx]" + location_string + "'!$A:$K,11,FALSE)),"" - "")"
> count = count + 1
> Wend
> 
> MsgBox ("Done")
> End Sub
> 
> ```
> 
> Below is the original post:
> 
> 
> I have a list of values on another sheet that will create part of the
>  string I need:
> 
> 
> 
> ```
> =CONCATENATE ((INDEX('Driver(s)'!$B$1:$B$48,'Driver(s)'!$G$3,1)),"Epic")
> 
> ```
> 
> this will set a cell to = 'O614Epic
> 
> 
> now trying to add a Vlookup to pull from:
> 
> 
> 
> ```
> S:\xxxxxxxxxxxxxxx\xxxxxxxxx\xx\xx\xx\[Random File Name.xlsx]0614Epic'!$A:$K
> 
> ```
> 
> Based on the drop down box, the ####Epic file will change to the
>  correct value as a string at the moment but can not get Vlookup to
>  pull from the correct workbook. I also need this to open non-opened
>  workbooks. Too much data to import into the Excel workbook itself.
> 
> 
> Thanks.
> 
> 
> * Ross
> 
> 
>