I have a "Main" workbook to create reports - that I copy information from other workbooks. (Lets call these the "Working Workbooks") There are like 85 Working Workbbooks that represent different locations. Each of the 85 Working Workbooks are set up exactly the same. In with these Files where the "Working Workbook" is located there are also monthly files - where on a certain day of each month the "Working Workbook" gets a copy saved and renamed into the appropriate month. See attachment of how files are set up.
So here is my question: Every month I am having to manually copy information from the 85 "Working Workbooks" to the MAIN file for reporting. I know I can to an formula to pull information from each of the 85 workbooks manually buy "= and select the appropriate cell" for information I need in the Main file - but when I do this - if when they save the "Working Workbook" as another file the my formula starts pulling information from the new file they created. I need it to always pull from that "Working Workbook" no matter if they do save a copy under a different name.
So here is my question: Every month I am having to manually copy information from the 85 "Working Workbooks" to the MAIN file for reporting. I know I can to an formula to pull information from each of the 85 workbooks manually buy "= and select the appropriate cell" for information I need in the Main file - but when I do this - if when they save the "Working Workbook" as another file the my formula starts pulling information from the new file they created. I need it to always pull from that "Working Workbook" no matter if they do save a copy under a different name.