Getting cell data from dynamically located and named file

[JamesLar]

This may be a little confusing.
I have a sheet that pulls data from another sheet currently using ='O:\inv\2019\[2019 Inventory.xlsm]Dec'!$W$111

Every year I have had to update the links when I save a new annual file from the template.

Is there any way I can have the directory and file referenced using the current file name as a variable. For example for the date I am currently using

=CONCATENATE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"-",(RIGHT(LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1),4),2)))

to have the sheet's date show the month and year called from the sheet tabs and the main filename.

I know if I use

=RIGHT(LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1),4),4)-1

in a file called 2019 Inventory.xlsm it will return a value of 2018. can that code be incorporated into my file reference so as I save the file as a new year it will always call on the previous year's directory and file?

Thanks in advance for any assistance.

 

[JamesLar]

either as a formula or vba would also work if it can do the same idea