Sometimes, you may want to warn the user that the workbook is still being opened and the data is not updated yet. This can be useful when you have formulas that link to other workbooks, and you want to avoid errors or incorrect results. In this article, we will show you how to create a warning message that appears when the workbook is still being opened, and how to update the data automatically when the workbook is fully loaded.
The basic idea is to use a formula that checks the status of the workbook, and returns a warning message if the workbook is still being opened, or the actual value if the workbook is fully loaded. One way to do this is to use the CELL function, which returns information about the formatting, location, or contents of a cell. For example, =CELL("filename") returns the full path and name of the workbook that contains the formula. If the workbook is still being opened, this function will return an empty string. We can use this as a condition to display a warning message.
Procedures
To create a warning message when the workbook is still being opened, follow these steps:
- In a cell where you want to display the warning message, enter the formula
=IF(CELL("filename")="","Workbook is still being opened",""). This formula will check if theCELLfunction returns an empty string, and if so, it will display the text “Workbook is still being opened”. Otherwise, it will display an empty string. - In another cell where you want to display the actual value from another workbook, enter the formula that links to the other workbook, such as
=SUM('[OtherWorkbook.xlsx]Sheet1'!A1:A10). This formula will sum the values in the range A1:A10 from the sheet named Sheet1 in the workbook named OtherWorkbook.xlsx. You can use any formula that links to another workbook as long as it is valid and returns a value. - Wrap the formula in step 2 with an
IFERRORfunction, and use the cell in step 1 as the value if error argument. For example,=IFERROR(SUM('[OtherWorkbook.xlsx]Sheet1'!A1:A10),A1). This formula will check if the formula in step 2 returns an error, and if so, it will display the value in cell A1, which is the warning message. Otherwise, it will display the actual value from the other workbook.
Example
To illustrate how this works, let’s create a simple example with two workbooks: Workbook1.xlsx and Workbook2.xlsx. In Workbook1.xlsx, we have the following data in Sheet1:
| Name | Sales |
|---|---|
| Alice | 1000 |
| Bob | 2000 |
| Carol | 3000 |
| David | 4000 |
| Eve | 5000 |
In Workbook2.xlsx, we have the following data in Sheet1:
| Name | Commission |
|---|---|
| Alice | 10% |
| Bob | 15% |
| Carol | 20% |
| David | 25% |
| Eve | 30% |
We want to calculate the total commission for each salesperson in Workbook1.xlsx, based on the commission rate in Workbook2.xlsx. We also want to display a warning message when Workbook2.xlsx is still being opened and the data is not updated yet.
To do this, we follow these steps:
- In Workbook1.xlsx, in cell B7, we enter the formula
=IF(CELL("filename")="","Workbook2 is still being opened",""). This formula will display the warning message when Workbook2.xlsx is still being opened. - In cell C2, we enter the formula
=IFERROR(B2*VLOOKUP(A2,'[Workbook2.xlsx]Sheet1'!$A$2:$B$6,2,FALSE),B7). This formula will multiply the sales amount in cell B2 by the commission rate in Workbook2.xlsx, using theVLOOKUPfunction to match the name in cell A2. If there is an error, such as #REF or #N/A, it will display the value in cell B7, which is the warning message. - We copy the formula in cell C2 and paste it to the range C3:C6.
The result is shown below:
| Name | Sales | Commission |
|---|---|---|
| Alice | 1000 | 100 |
| Bob | 2000 | 300 |
| Carol | 3000 | 600 |
| David | 4000 | 1000 |
| Eve | 5000 | 1500 |
| Total | 15000 | 3500 |
| Workbook2 is still being opened |
Note that the warning message appears in cell B7 and C7, because Workbook2.xlsx is still being opened and the data is not updated yet. When Workbook2.xlsx is fully loaded, the warning message will disappear and the actual values will appear.
Other approaches
There are other ways to create a warning message when the workbook is still being opened, such as using the INFO function, the WORKBOOK function, or the ISREF function. You can also use conditional formatting to change the color or font of the cells that contain the warning message. However, the approach we showed above is simple and effective, and it works for most cases.
