Guidebook: Excel Tip #2 - VLOOKUP

Vlookup is the essential function for comparing data in one list to data in another list.

In this example our first table has a list of addresses but no postal codes. The second table has a list of addresses WITH postal codes. We'll use the VLOOKUP function to find the right postal code in the second table and add the postal code to the first table.

For the VLOOKUP function to work properly both lists must have some data in common. And both sets of data should be in the same Excel workbook. Otherwise we have nothing to use to make a match. In this example the Address column will be used to find a match.


Let's add the Postal Code to the first table...
1. Create a new column to store the postal code in our address table.
2. The function in this cell is
VLOOKUP(lookup, value_table_array, col_index_num,[range_lookup])
lookup = the cell containing the address to match
value_table_array = the entire range of columns containing the second table
- click and select the columns you want to search for a match and the columns containing the data to find.
col_index_num = the column number containing the data you want to find in the table you just selected
[range_lookup] = can be TRUE or FALSE - specifies whether or not the data should match exactly

In the figure below we're going to look for the Address in C2 by searching in table I:J.
In the table I:J, Column #1 contains the address and Column #2 contains the Postal Code.
We're happy returning the first match rather than an exact match.


The function returns the correct postal code so we use "Fill Down" to apply the same function to the rest of the cells in the same column.


Now that you've had an introduction to VLOOKUP you probably can't believe how much time you spent comparing lists of data in the past.

Have fun!

Click here for the VLOOKUP help article on the Microsoft Office website.

Windows SharePoint Services and ASP .NET 2.0!

After a day of many, many server upgrades to our internal systems today I received a rather sad message from our internal sharepoint site when I went to post an announcement about the excitement of Windows Server 2003 SP1:

The Windows SharePoint Services virtual server has not been configured for use with ASP.NET 2.0.xxxxx.0. For more information, please refer to Knowledge Base article 894903 at http://go.microsoft.com/fwlink/?linkid=42660

Although this seems to be an uncharacteristically helpful message in that it provides the Microsoft knowledge base ID number, the KB itself was less than straightforward.

The main solution offered by the KB is to run the command line command:
stsadm.exe -o upgrade -forceupgrade -url http://URLOftheVirtualServer

The problem is that the version of stsadm.exe that exists on our internal server (Windows Small Business Server 2003) does not support the "upgrade" command. It offers a great deal of helpful commands, but no upgrade.

If you find yourself in this same situation, you can manually adjust the web.config file for your SharePoint site.

You need to make 2 changes:

1. (old tag) <trust level="WSS_Minimal" originUrl="" />
(new tag) <trust level="WSS_Minimal" originUrl="" processRequestInApplicationTrust="false" />

2. (old tag) <pages enableSessionState="false" enableViewState="true" enableViewStateMac="true" validateRequest="false">
(new tag) <pages enableSessionState="false" enableViewState="true" enableViewStateMac="true" validateRequest="false" enableEventValidation="false"> <namespaces> <remove namespace="System.Web.UI.WebControls.WebParts" />

The KB does a good job of explaining why it wants you to run these updates so I'll leave that up to Microsoft. This post should hopefully just help you out in the event that you've spent the last 8 hours running the 14 steps to upgrade your Small Business Server to SP1 and you just want to go home.

Guidebook: Excel Tip #1 - Auto Filter

Auto Filter is a great feature for managing a table of data in Excel.

Start with a table of data - make sure the colum headings are labeled.



Select all the columns that you want to work with (or the entire worksheet).
From the Data menu choose "AutoFilter".



Now, notice the drop-down arrows beside each column name.



Selecting an entry from the drop down menu will filter the data based on your selection.



Give it a try!
Want to get more sophisticated? Select the "Custom..." option.

For a more detailed description of this feature checkout the online training on the Microsoft Office website.

---

Keep an eye out for "Guidebook" posts to bring you quick tips on how to make your job a little easier. Feel free to email us requests or tips you want to see posted.