# Using a cell value as part of a path for another file



## h4wk1n545 (Dec 21, 2022)

HI All

I am currently working on a sheet that deals with Driver infringements and gathers its data from 20 other sheets, this needs to be easily updateable each year or if staff members leave and are replaced.

What I currently have is 20 sheets each named > Staff member Data Input 

The Path for this file is 


C:\Users\hawki\OneDrive\Desktop\INFRINGEMENT\2022\bob  hope Data input.xlsx

and a master sheet named > Infringement Log

the path for this file is 

C:\Users\hawki\OneDrive\Desktop\INFRINGEMENT\2022\Infringement log.xlsx

What I currently have is a Vlookup 
=VLOOKUP(A1,'C:\Users\hawki\OneDrive\Desktop\INFRINGEMENT\2022\[Book2.xlsx]Sheet1'!$A$1:$B$10,2,FALSE)

What I am hoping is possible is to add Cell values into the path in the formula in the area i have coloured Yellow
=VLOOKUP(A1,'C:\Users\hawki\OneDrive\Desktop\INFRINGEMENT\C1\D1Sheet1'!$A$1:$B$10,2,FALSE)

Which if my thinking is right would mean with each new member of staff i could Copy the sheet rename as needed and change cell D1 to the name of staff member and formula would work with out the need to change.

same as the start of each new year i could make a new folder for that year copy previous years workbooks and change cell C1 and again sheets would automatically update.

I hope some of this makes sense as it wasn't the easiest to explain any help or advice or a simple no it cant be done would be appreciated as im currently ripping my hair out trying to solve it

Cheers in advance 

Rich


----------



## offthelip (Dec 21, 2022)

welcome to the forum,
The way I would do this is to use VBA to create the formula,  this is likely to allow your workbook to run faster. note you haven't said where you want hte formula so I put it in A1

```
Sub test()
'=VLOOKUP(A1,'C:\Users\hawki\OneDrive\Desktop\INFRINGEMENT\C1\D1Sheet1'!$A$1:$B$10,2,FALSE)
'=VLOOKUP(A1,'C:\Users\hawki\OneDrive\Desktop\INFRINGEMENT\2022\[Book2.xlsx]Sheet1'!$A$1:$B$10,2,FALSE)
Range("a1").Formula = "=VLOOKUP(A1,'C:\Users\hawki\OneDrive\Desktop\INFRINGEMENT\" & "\" & Range("c1").Value _
 & "\[" & Range("d1").Value & "]Sheet1'!$A$1:$B$10,2,FALSE)"
 
End Sub
```


----------



## h4wk1n545 (Dec 22, 2022)

Thanks this was exactly what I was looking for.


----------

