VBA Access Common Cell in Many Different Sheets in an External Workbook

Using Microsoft ® Office Excel 2003 (11.8220.8221) SP3, I started with a sheet, Sheet7, that pulled the first non-blank error message found in a common cell (i.e., A7) in Sheet3.1, Sheet3.2, ... , Sheet3.15, Sheet3.16. All of these sheets resided in a single workbook, and worked correctly using the following VBA code snippet:

Please Login or Register  to view this content.

As you can see, sixteen (16) sheets exceed the Excel nested levels of functions (i.e., seven (7); e.g.,

Please Login or Register  to view this content.

Therefore, a function had to be used. As I continued to develop this workbook, I encountered another Excel limitation which could not be overcome primarily by choice and secondarily by knowledge.

Each error message cell in the workbook has conditional formatting associated with it. That is, if the cell is not blank, it is turned bright pink to get the user's attention and inform them of the error message and the sheet name where the error was detected. There is an Excel limitation of 2050 rows (in theory) containing conditional formatting. The evidence revealed itself in that I would get a popup error indicating the workbook could not be saved. The highlighted cell is not an option to be discarded. The unfinished workbook exceeds 50MB and consists of twenty-six (26) sheets plus some documentation sheets. So, I created another workbook with conditional formatting in 3050 rows in one (1) sheet which was successfully saved. The only alternative solution I could think of was to break the workbook into many workbooks. However, in trying this solution I encountered the problem for which I am seeking a solution.

As the title suggests, the function above must now be able to reference not only a different sheet, but a different workbook. In order to attempt a solution, I created a pair of test workbooks (i.e., MS Excel - Primary (Test).xls, MS Excel - Secondary (Test).xls). The former workbook contains three (3) sheets (i.e., Sheet1, Sheet2, and Sheet3). Sheet1, cell A2 contains: MS Excel - Primary (Test).xls / Sheet1. Sheet2, cell A3 contains: MS Excel - Primary (Test).xls / Sheet2. Sheet3, cell A4 contains: MS Excel - Primary (Test).xls / Sheet3. The latter workbook contains one (1) sheet (i.e., Test). Sheet1, cells A2, A3, and A4 contain:

Please Login or Register  to view this content.

The function is:

Please Login or Register  to view this content.

The same results were obtained regardless whether the commented statements were activated or deactivated in various combinations. So, I concluded they had neither a successful nor a failure impact on the results. The function sort of works, but not to my satisfaction.

When I make a change in an unused cell in the Secondary workbook and Test sheet, the result is the same in cells A2, A3, and A4 (i.e., #Value!). This is unsatisfactory.

When I make a change in an unused cell in the Primary workbook and the Sheet1 sheet, and return to the Secondary workbook and the Test sheet, I observe the correct values. That is, the Secondary workbook, Test sheet, and cells A2, A3, and A4 contain MS Excel - Primary (Test).xls / Sheet1, MS Excel - Primary (Test).xls / Sheet2, and MS Excel - Primary (Test).xls / Sheet3, respectively. This is also unsatisfactory because the result is only obtained by making a change to the sheet containing the value to be assigned by the function.

How can a function reference an external workbook in this case?

Thank you in advance for your response. Sorry for the problem description size, but I didn't know how else to describe it to be thoroughly understood.