Combining Text Strings with Worksheet Data

Though I have used Excel's CONCATENATE function to combine values and text strings on
a routine basis, I recently saw in another spreadsheet that the use of the & symbol is a little easier to follow when building the string.

Rather than =CONCATENATE("The value in Cell A2 is", TEXT(A2,"0.00"))

I can simply type="The value in Cell A2 is"&TEXT(A2,"0.00")

For me the ampersand(&) makes it much easier to follow and there isn't the issue of matching the right and left parenthesis.

Not an earth shattering find, but a valuable one none the less! ;-)

Conditional Formatting with AND

The following is an example of using Conditional Formatting
to highlight a cell if the value is between 2 numbers.
The syntax is shown in the following graphic

Conditional Formatting - Travel Calendar Example

A spreadsheet was used to create an Office Travel calendar to keep track of who was in the office and whether or not they are on leave or business travel.

Conditional formatting was used to color the cells for employees who are out of the office. The conditions used are
1.) If a person is on Leave, the cell is colored red
2.) If the person is anywhere else other than "Office" the cell is colored yellow.
3.) If the office is closed, i.e. it is a "Holiday", the cell is colored green.





The 2 columns following the employee's name are used to highlight the fact that someone is either on Travel (T) or in Leave (L) status, and these cells also used conditional formatting based on the values in the weekday cells located to the right by using the COUNTIF function in Excel.





The leave status was pretty simple to check. Using the COUNTIF Function to see if the word "Leave" appears in any of the cells

The Travel column was a little more tricky since the search value is not confined to
one or two words, but rather could be any city or location. Since there are 5 days a week, the formula has to take into account Holidays and previously scheduled leave. For example if Monday is a Holiday and the employee is on Leave on Friday, that means the word Office should appear no less than 3 times that week. The COUNTIF
function is used to do the math on this one and the formula for conditional formatting is shown below:

=COUNTIF($E7:$I7,"Office")<(5-(COUNTIF($E7:$I7,"Holiday")+COUNTIF($E7:$I7,"Leave")))

And the results are shown below