Hello,
I have more than 200 Excel workbooks which are all formated according to 3 different templates.
I extracted the list of all the workbooks in the folder and created a table in "MasterRegister", where I would like to perform the same calculations on each workbook without opening them and adding the results in the columns of my MasterRegister.
This is my formula in the MasterRegister for each category.
I use the indirect(concatenate) to refer to the workbooks names I extracted.
= Countifs(Indirect(Concatenate([FileName]TabName!$B10:$B500,"Yes",Indirect(Concatenate([Name of the file]TabName!$C10:$C500,"ABC"))
The problem is it only calculates if the workbook is open, I cannot do any change to the workbooks as they are read only data and in addition, I'm not ready to open the 200 files and the workbooks are added regularly
Is there a way to do this? I saw online Sumproduct function may be a solution but I'm not sure where to start.
I also need the MasterRegister to update with the new files I will add in the folder in the future.
I don't mind using macros or PowerQuery but I don't know where to start.
My MasterRegister is then queried in PowerBi to get the data.
Thanks in advance for your assistance, I've tried all day but it doesn't work.
