I have 12 workbooks, one for each month. I have a 13th workbook in which I wish to record selected data from each month (which is then totalled for the year).
The monthly workbooks are all closed except the current working month. The problem I have is that in the 'yearly' workbook (when adding a new month) I must manually 'open' the new monthly workbook for each cell formula change or addition (about 30 times).
This also has to be executed even if a monthly workbook is actually 'open' and in current use - the formula includes the path with all subdirectories and drive letter. For the closed workbooks, that's necessary, but for the open workbook?
I seem to remember in the distant past, that there is a way around this (using 'Sumproduct'?) where once the formula is added no further action is required. The path is added when the monthly workbook is closed.
The main problem is that the open and in use workbook data is not updated in the 'yearly' workbook. I have to go through the above 'opening' procedure each time but with the addition of altering a formula in each cell before I can 'open' an already 'open' workbook.
Sorry for the lengthy description, but I do feel there must be an easier way to extract data from closed workbooks.