TOP EXCEL WEB SITES - WORKSHEET FORMULA TIP

Created by David Hager

If you want to find information posted in Internet newsgroups by a particular
person, you can use the following technique. First, make a 4 column list as 
shown below (on Sheet1).

A      B        C                D
Mike   Jomes    mjomes@abc.com   Mike Jomes mjomes@abc.com 
Kim    Jimes    kjimes@xyz.com   Kim Jimes kjimes@def.com
etc. 


where column D concatenates the information in columns A-C. Then, use that
column as the list for Data Validation in a cell. Give the list a defined 
name such as addList if it is on another worksheet than the HYPERLINK formula
shown below, as it is in this example). To make the list dynamic, use:

=OFFSET(Sheet1!$D$1,,,COUNTA(Sheet1!$D:$D),)

Create the Data Validation for cell D1 on Sheet2 (by using =addList as the
lookup list). Then, type this formula in A1 on Sheet2 and fill to C1.

=OFFSET(Sheet1!A$1,MATCH($D$1,addList,0)-1,,,)

Finally, type this formula in A2 on Sheet2:

=HYPERLINK("http://www.deja.com/profile.xp?author=%22"&$A$1&"%20"&$B$1&"%22
%20%3c"&$C$1&"%3e&ST=PS")

This creates a hyperlink that will return the newsgroup postings of the 
person you have selected from the list. I use this technique to obtain
information posted by specific individuals in the Excel newsgroups.