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
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
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.
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
Code:
=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)))
I know if I use
Code:
=RIGHT(LEFT(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1),4),4)-1
Thanks in advance for any assistance.