We currently have 16 individual workbooks that we have 16 individuals tracking data in.
We have created a single Consolidated Excel File to bring all of the data together from the other workbooks.
The Workbooks are stored in a folder on the server on a Drive which is labeled Q:.
Each of the cells on the Consolidated Workbook have a reference to a cell on the individual workbooks. The cell reference looks like this:
If the files are moved or if the user's computer does not map the drive where the files are located as the Q:Drive, then the values are not displayed on the Consolidated File.
We have written a Macro which also opens up all of the individual files, because the references to the files do not work on closed notebooks.
We would like to try to use a relative path in the references so that the files can be placed anywhere as long as all of the files are kept together.
Thoughts on the best way to change the references?