PDA

View Full Version : Solved: Search list with Vlookup



austenr
04-17-2005, 10:59 AM
I have been given an assignment to compile a listing of companies we service. I need a way for the user to input the company they are looking for and have it pop up with the following information. For example:

John Brown
Hours 8:00 to 5:00
Backup: Sally Jones


I know that this would be a good time to use ACCESS but I am forced to use EXCEL because our IT department in their infinant wisdom decided we needed Power Point more than ACCESS.

I was thinking about using Vlookup but am open to suggestions. Thanks in advance for your help.

Jacob Hilderbrand
04-17-2005, 11:18 AM
VLookUp should work fine for you. Can you post an example workbook showing the layout of your data?

austenr
04-18-2005, 09:12 AM
Here is my test file

mdmackillop
04-18-2005, 09:46 AM
Hi Austen
You could do this with a userform The attached example shows both a listbox and combobox, although you would use only one. Depending upon your data, a sorting routine may be required to present your companies in logical order, if you with to go down this route.

austenr
04-18-2005, 01:55 PM
Thanks MD I like that approach. One more thing I added something to the List box you made. All that I really need now is to have all the fields filled in once you pick a group. I attached your updated file to this reply. Almost there. Thanks a lot.

Also, my VBA is a little rusty, where do I change the name of the button when you open the workbook?

austenr
04-18-2005, 02:34 PM
O.K. I got most of it the way I want it. There is a problem though. I deleted some of my groups in column B and they are still showing up in my list box. Can anyone help fix this? If I get that I got what I need. File attached.

mdmackillop
04-18-2005, 02:52 PM
To change the button text, right click on the button first, then you can edit the text. The button is added from the Forms toolbar.

This will ignore blank values for your listbox

Private Sub UserForm_Initialize()
Dim Cel As Range
For Each Cel In Intersect(Range("Listing"), ActiveSheet.UsedRange)
If Cel.Text = "Group:" Then
If Cel.Offset(0, 1).Text <> "" Then
ListBox1.AddItem Cel.Offset(0, 1).Text
End If
End If
Next

End Sub


I've attached a sample incorporating DRJ's Bubblesort routine
http://www.vbaexpress.com/kb/getarticle.php?kb_id=103

austenr
04-18-2005, 03:54 PM
hey thanks..i figured it out. thanks again .. mark this solved