Let’s say you have a validation check in your file and you want to display a message based on the validation status. Here is one way to do that.
This avoids using a cell and instead uses a text box to display the message.
Below is validation sheet with the message to display.
We could link other cells to the cell C1. The problem is that you may not have space in the cell structure of each sheet to include another formula.
Note: I have named cell C1 rMessage – see the Name Box in the top left of the image above.
I can use this name throughout the file to refer to cell C1 in this sheet.
In another sheet I can create text box (Insert ribbon tab – far right-hand side).
With the text box selected I can click in the Formula Bar and press = and type rMessage and press Enter. See image below.
The text box is linked to the named range.
This text box can be copied and pasted to other sheets to display the message.
If a validation error occurs the text box automatically updates.
Text boxes can be more easily positioned than cells, allowing more flexibility in the placement of the message on each sheet.
The advantage with using a cell to display the message is that you can apply a conditional format to highlight errors.
You can’t conditionally format a text box.
Please note: I reserve the right to delete comments that are offensive or off-topic.