May 13, 2004

Now You See it, Now You Don't

Sometimes you'll find a certain cell or cells are on 'prime real-estate'. You might want to enter a formula in a particular cell, but at the same time you might want that same cell to display a message of some type under certain conditions.

Let's say I want a cell to display the message "Check Payments" 10 days or less before the end of every month. Here's a formula that will do that for me.

=IF(TODAY()>DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)-10,"Check Payments","")

At the same time, I want to enter a different formula into the same cell, but because it's not possible to enter more than 1 formula, how can I overcome this this limitation? By using some Conditional Formatting and a clear AutoShape.

First of all, let's use part of the above formula and insert it in the Conditional Formatting dialog box with some modifications. (You'll notice there is no FALSE in the below formula, it isn't necessary in this case)


Also, all I want is for the background color and font color to change at this time, the "Check Payments" message is entered at a later stage. (I've chosen a black background and black font because I intend to use white text for the clear AustoShape to the "Check Payments" message stand out)

Next, enter the actual formula you want in the cell. Lets say you want the current year, so try =YEAR(TODAY()).

After this, place a rectangular AutoShape over the cell, using the ALT key to align it automatically against the sides of the cell. Use the Fill Color menu to make it clear by selecting No Fill, then hide the lines with the Line Color menu by selecting No Line. After this enter the text "Check Payments" into the AutoShape.

After this make the text of the AutoShape white and you are finished. When the end of the month approaches, this is what you'll see. (The "Check Payments" message has appeared, and as you can see, the =YEAR(TODAY()) formula is still entered in the cell underneath)


Comments: Post a Comment

This page is powered by Blogger. Isn't yours?