I am working with user data that is stored in identical workbooks (i.e. the data needed is in the same cell location for each user, on their respective workbook), in folders that all match name and format. I am looking to collate data from multiple users, and create a solution that allows for new users to be regularly added/removed without manually adjusting file paths.
e.g. The data for user 'Ben' will be in ='S:\Staff\Ben\Timesheets\[Week 85.xlsx]Time Sheet'!$G$17
and the same document for 'Lisa' will be in ='S:\Staff\Lisa\Timesheets\[Week 85.xlsx]Time Sheet'!$G$17
as you can see, the only difference in the path is the staff member's name, and this is true for all workbooks I'm looking to collate.
If I am looking to collate this data, I'm hoping that I can have a formula where Excel autocompletes the path name using a list of staff names.
e.g. If I put the name 'James' in cell B5, I could use a formula such as ='S:\Staff\(=B5)\Timesheets\[Week 85.xlsx]Time Sheet'!$G$17 However this example formula does not work. Is there a formula that would work?
I'm attempting this solution as staff are regularly added and removed, and I would like to avoid having to 're-link' this data for each new user, when an automatic solution can be implemented from a list of staff names.
Many thanks.
Bookmarks