I need to use Excel 2007 to reference value from unopened/closed Excel file with a formula or VB
For example, I want to turn this:
=INDEX('M:\Bills\Red River Bank\[Checking-4074-2018.xlsm]12-18'!$H$1,1,1)
Into something like this:
=INDEX("'M:\Bills\Red River Bank\[Checking-4074-20" & $C$1 & ".xlsm]12-18'!$H$1",1,1)
so that I can dynamically change the values in my workbook based on values in another CLOSED Excel file by using the value in my active workbooks $C$1 cell.
Why I am doing this is that at the end of the year, I close
M:\Bills\Red River Bank\Checking-4074-2018.xlsm, take my template
M:\Bills\Red River Bank\Checking-4074-20xx.xlsm, and rename it M:\Bills\Red River Bank\Checking-4074-2019.xlsm, then it references the last years file to populate key fields that are then used dynamically in the new file.
Google has failed me as all I can find are deprecated methods using stuff that the links to are dead.
Any help guys?