### Force Uppercase In Cell(s)

The other day my manager asked me an interesting question. He wanted to know if there was a way for force the contents of a cell to be all uppercase.

I thought for a minute and remember reading about how to do this. Well I could not remember where I learned of this trick but I did remember how to do it.

You can force the cell to have upper case using Data validation and the =Exact function.

From the menu select Data > Data Validation

In the Settings tab enter =EXACT(B7,UPPER(B7)) in the formula field.

But we can jazz this up a bit. In the Input Message I can enter a message to display when the cell is selected.

For the Error Alert, I also create a message (since generic errors are sometimes confusing).

Now whenever someone selects the cell they get a prompt onscreen as to what type of data is expected and if they enter in the data incorrectly, they get an error message explaining how to correct the data.

So how does this work? Well Exact compares two values. In this case the Value in cell B7 compared against the Uppercase conversion of cell B7. The uppercase function converts the entire cell to uppercase.

So the next time someone asks you for ensure the values of a cell are uppercase, consider the above, break out your mad Excel skills, and WOW them.

INTERMEDIATE, EXACT, UPPERCASE, DATAVALIDATION

### INDEX part 2

Last post I introduced the Index formula. In this post I want to continue explaining how Index works and why you should understand it.

From my previous post, we now know that Index allows you to get a value at an intersection of any give row and column. In this example I return the 4th row down and the 3rd column to the right for the data range.

 Item Price Manufacturer Result Formula Hammer 7.53 ACME West Bird Seed 13.58 ACME East ACME West =INDEX(A2:C11,4,3) Large Magnet 100.00 ACME South Rocket Skates 68.54 ACME West Safety Helmet 18.00 ACME North Large Spring 240.00 Scotland First Aid Kit 72.00 ACME East Cannon Ball 45.00 ACME West Rope 12.00 ACME North Paint 22.50 ACME South

Now you can also return an entire row or column from the same table. Perhaps I want to know the total Price of all items in my table. I could write the formula =SUM(INDEX(A2:C11,,2)). And yes I know I could just Sum the column but this is an article on the Index formula. Our example formula ends ,,2 This tells the INDEX formula to return the entire column. If it ended in ,2, then Index would return the row.

Remember the syntax of INDEX. By omitting the Row Or Column reference I can return entire rows or columns.

=INDEX(Range,Row,Column)

 Item Price Manufacturer Result Formula Hammer 7.53 ACME West Bird Seed 13.58 ACME East 599.15 =SUM(INDEX(A2:C11,,2)) Large Magnet 100.00 ACME South Rocket Skates 68.54 ACME West Safety Helmet 18.00 ACME North Large Spring 240.00 Scotland First Aid Kit 72.00 ACME East Cannon Ball 45.00 ACME West Rope 12.00 ACME North Paint 22.50 ACME South

=INDEX’s unique ability to return entire rows or columns from ranges or arrays is very powerful.
Perhaps my dataset is large, spanning cells A2 thru AZ60000 (A2..AZ60000).  I can name that range LargeDataSet

I can then name individual columns in that dataset by referencing the Array. If I want to create another Name for the Index column (column 1) I would write the name formula as follows.

ITEM  =INDEX(LargeDataSet,,1)

This now allows me to lookup any item by using the =MATCH function.

 Item Price Manufacturer Result Formula Hammer 7.53 ACME West Bird Seed 13.58 ACME East 5 =MATCH("Safety Helmet",Item) Large Magnet 100.00 ACME South Rocket Skates 68.54 ACME West 18.00 =OFFSET(A1,MATCH("Safety Helmet",Item,0),1) Safety Helmet 18.00 ACME North Large Spring 240.00 Scotland First Aid Kit 72.00 ACME East Cannon Ball 45.00 ACME West Rope 12.00 ACME North Paint 22.50 ACME South

In this example I use my name Range Item (A2..A30) to find the row Safety Helmet is on (row 5)
I then use Offset much like I would use Vlookup to return the price.

Using this method is comparable in speed to using Vlookup  when you are doing only 1 lookup. But I can look to the left with this method (Vlookup only looks to the right).

INDEX is useful in many other ways but that another future post.  Give INDEX a try and add it to your Excel knowledge.