I have seven workbooks in a folder on a Server. Each workbook carries the name of one of the departments in my organization.
Within each workbook are twelve worksheets. Each of the twelve worksheets has as its name, one of the months of a calendar year.
All worksheets have the same structure in that is contains categories of expenditure and the items that fall within that category. For example, there is a category Equipment Purchases, and this category consists of several individual equipment purchases.
Every month information is entered for each department for the individual items making up a particular category, and the total for each category is calculated. So there is a total on each worksheet for each category of expenditure.
Apart from the seven workbooks, I have an eighth workbook called Summary. This Summary workbook has twelve worksheets. Each worksheet is named using a calendar month, a dash and the word summary for example October-Summary; November-Summary etc.
In the summary workbook, only the categories of expenditure is recorded, and the total expenditure for each category must be gotten from the seven workbooks by month and linked to the Summary workbook.
To link the worksheets in the Summary workbook to the other seven workbooks, I use the following methods of linking a s follows:
='\\10.19.0.26\acc_fa_shfldr\Fiscal 2020-2021\VBA Saved Template - Copy\[Land_Management.xlsm]#REF'!B21
=INDEX('\\10.19.0.26\acc_fa_shfldr\Fiscal 2020-2021\VBA Saved Template - Copy\Recurrent\[2-Information.xlsm]#REF'!$B$24:$I$24,1,1)
The first of the method gave me the error #REF when I open the Summary workbook. The error #REF corresponds to where the worksheet name within one of the workbook should be. The file path, the name of the workbook and cell references are ok.
For clarity, in the first link, VBA Saved Template-copy is the name of the folder on the Server, Land_Managment is the name of the Workbook and! B21 is the cell which contains the data. #REF occurs when the link is broken.
When the link is not broken the name of worksheet is exists where #REF occurs when there is an error. Here is an example of an unbroken link ='\\10.19.0.26\acc_fa_shfldr\Fiscal 2020-2021\VBA Saved Template - Copy\[Land_Management.xlsm]October'!B21
In an attempt to get around this problem I tried using the index function as depicted in the second method of linking the summary workbook to the other departmental workbooks.
However, I got the same error #REF.
My question is, is there a workaround or solution to the problem of links breaking when opening a workbook that contains links to data in other workbooks.
Within each workbook are twelve worksheets. Each of the twelve worksheets has as its name, one of the months of a calendar year.
All worksheets have the same structure in that is contains categories of expenditure and the items that fall within that category. For example, there is a category Equipment Purchases, and this category consists of several individual equipment purchases.
Every month information is entered for each department for the individual items making up a particular category, and the total for each category is calculated. So there is a total on each worksheet for each category of expenditure.
Apart from the seven workbooks, I have an eighth workbook called Summary. This Summary workbook has twelve worksheets. Each worksheet is named using a calendar month, a dash and the word summary for example October-Summary; November-Summary etc.
In the summary workbook, only the categories of expenditure is recorded, and the total expenditure for each category must be gotten from the seven workbooks by month and linked to the Summary workbook.
To link the worksheets in the Summary workbook to the other seven workbooks, I use the following methods of linking a s follows:
='\\10.19.0.26\acc_fa_shfldr\Fiscal 2020-2021\VBA Saved Template - Copy\[Land_Management.xlsm]#REF'!B21
=INDEX('\\10.19.0.26\acc_fa_shfldr\Fiscal 2020-2021\VBA Saved Template - Copy\Recurrent\[2-Information.xlsm]#REF'!$B$24:$I$24,1,1)
The first of the method gave me the error #REF when I open the Summary workbook. The error #REF corresponds to where the worksheet name within one of the workbook should be. The file path, the name of the workbook and cell references are ok.
For clarity, in the first link, VBA Saved Template-copy is the name of the folder on the Server, Land_Managment is the name of the Workbook and! B21 is the cell which contains the data. #REF occurs when the link is broken.
When the link is not broken the name of worksheet is exists where #REF occurs when there is an error. Here is an example of an unbroken link ='\\10.19.0.26\acc_fa_shfldr\Fiscal 2020-2021\VBA Saved Template - Copy\[Land_Management.xlsm]October'!B21
In an attempt to get around this problem I tried using the index function as depicted in the second method of linking the summary workbook to the other departmental workbooks.
However, I got the same error #REF.
My question is, is there a workaround or solution to the problem of links breaking when opening a workbook that contains links to data in other workbooks.
