Hi,

I am looking for some help. I have a sumproduct formula that is referencing a file saved in a network location. Below is that formula. The part I'm looking for help on is highlighted yellow. each month the file name changes where you see the date.

Is there a way to make this formula dynamic, for example that part of the formula references a cell where i can change the date, or reference a file path etc.

SUMPRODUCT(--('Q:\Production\FACTORY BUILD_WIP PROFILE\Build Profile Exports - Kieran\[Build Profiles for Production - 25-May-2021.xlsx]P3 Act_GS12 Annual'!$E$5:$KB$5>=DATE(YEAR($L$3),MONTH($L$3),1))*('Q:\Production\FACTORY BUILD_WIP PROFILE\Build Profile Exports - Kieran\[Build Profiles for Production - 25-May-2021.xlsx]P3 Act_GS12 Annual'!$E$5:$KB$5<=DATE(YEAR($L$3),1+MONTH($L$3),1)-1)*('Q:\Production\FACTORY BUILD_WIP PROFILE\Build Profile Exports - Kieran\[Build Profiles for Production - 25-May-2021.xlsx]P3 Act_GS12 Annual'!$B$6:$B$15=A24),'Q:\Production\FACTORY BUILD_WIP PROFILE\Build Profile Exports - Kieran\[Build Profiles for Production - 25-May-2021.xlsx]P3 Act_GS12 Annual'!$E$6:$KB$15)
I have has success doing this in the past with Powerquery quite easily but really cant figure in out from a formula perspective so any help is greatly appreciated,

Many thanks!