Auto update of values from other excel sheet based on changes in one cell

[ramakrishnavbms]

Hi all,
I am sorry if this is a repeated thread but i couldn't find an answer for the same.
i want to update values fon an excel sheet on a month by month basis.
So i have jan to dec folders and excel sheets under each folder , i want to update my monthly forecast sheet based on the numbers in the folder
I want to just change the cell in my monthly forecast sheet to Jan or Feb and the values should be pulled from the closed excel files in the monthly folders.
I tried concatenation but it gave me #REF .. Is there a way to do it other than using "pull" ??

Thanks

[daffodil11]

A1 = month on forecasting sheet, which should also correspond to the name of a file


Assuming the sub folders are in the same folder as the current file, and that there's relevant data on Sheet1, in cell B2 that you're pulling:

='[MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)&A1&"\"&"A1".xlsx]'Sheet1!B2"

Assuming I had your files and folders, and I put JAN in A1 this becomes:

='[C:\daffodil11\documents\monthlycallforecast\JAN\JAN.xlsx]'Sheet1!B2