Excel

[Tina1967]

Hi - I would really appreciate help with please.  I am not sure if I am explaining what I need properly so please do ask for clarification if you think you can help me.

I have a tracker that has links to external workbooks which are timesheets.  Every month I have to manually change the links e.g. I have a folder called November which has a tracker called November and all timesheets for November.   Timesheets are called Employee 1 - November, Employee 2 - November etc.  The only reason I use the naming convention is so that I can open multiple timesheets for one person if needed.

The following month I would copy the November folder and rename everything December and then manually update the links in the tracker  This needs to be done for 130 employees.

This is the formula I am using ..

=VLOOKUP(D2,'https://mydrive/Payroll/2021/12 2021 Payroll/Timesheets/[Employee 1 - December.xlsx]Timesheet'!$B$4:$L$70,9,FALSE)

Does anyone know a way that I can use the value of a cell to dictate which workbook should be referenced in the formula above?  i.e. Use the result of the formula in Cell C3 etc to populate the name of the workbook in the formula.

[#2]

@Tina1967 Am not a big fan of such external links and copy/past/move to different folders, but you could use Find & Replace (Ctrl-H) in your tracker sheet to search for the previous month's name in all formulae and replace it with the current month's name.

So, you would Find "November" and Replace with "December". No need to do that manually.

But, if you would really like to save time, consider using Power Query. You can connect to an entire folder, including subfolders in one go and "read" the content of all files in these folders. Set it up correctly once and next time you just press "Refresh All" and all new files will be included in the tracker.

It will take some time to get started, but once you grasp it, you can't live without it. The link below is a good starting point. Chapter 9 in particular for your situation, but start from the beginning.

https://exceloffthegrid.com/power-query-introduction/ 

[#3]

@Riny_van_Eekelen Thank you so much for your reply, I will look at Power Query and will use Find and Replace.