Dynamic file paths. HELP!

Thank you everyone for your help in advance. Im sure the solution to my problem is an easy one, but I can't figure it out.

Things I'm trying to achieve with my worksheet:
Allow multiple (most not excel savy) people use this spreadsheet with ease.
Pull information from 52 different files on my computer. All saved in the same directory.
Have the filepath change within the cell to match user input information. Eg. Year or store number.

Things I've already tried:
I've used the concatenate formula to string everything together, but it doesn't work.
Copy all and paste values. Works, but loses original formulas, making it harder to switch between store number, year, ect.

I may have to do something with vba for this, but don't know where to start. I've got formulas on lock, but I'm self taught in VBA.
I attached my file so you can check it out and give me feedback. Cells I used when doing concatenate start in cell AJ9.