Dynamic file paths. HELP!

**christhweatt** · 10-07-2013, 03:09 PM

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.

**daffodil11** · 10-07-2013, 03:54 PM

I know the other files are grouped together, but is this file located with the other 52?

**christhweatt** · 10-07-2013, 04:01 PM

At this moment, no. It is on my desktop. Should I put it there?

**daffodil11** · 10-07-2013, 04:08 PM

I usually place the files and subfiles together to prevent filepathing issues when I move my workbooks around.

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-1) can come in handy for that though, as it will autotarget the directory your file is in, and can then easily target any other files in that same folder no matter where you move it and its contents.

I'm still working on a way solve your issue cleanly without resorting to paste values, so I'm beginning to think it may be a VB solution.

What are the ranges in your workbook you ultimately need to fill in with values? Just the spots with #REF?

**christhweatt** · 10-07-2013, 04:17 PM

I will initially need to fill columns B through N with information from my 52 wb's. Each row(week) is a different workbook. I also put my cell references that the data will be pulled from in BJ11-BU11.