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

[h4wk1n545]

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]

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]

Thanks this was exactly what I was looking for.