Rolodex

[Legacy 53668]

I'm trying to create a rolodex in excel. My spreadsheet consists of two tabs called database and rolodex. The database tab consists of columns with account numbers, names, address, phone numbers etc. The rolodex tab will allow a user to select one account and only pull in information for that one account. My problem is that I would like to design the spreadsheet so that the user can select from a drop down list of either account numbers or account names. I can design it so that they select one of these options, but I can't figure out how to give them the ability to select either one. Ideally I would have a pop up box (similar to the way a calendar would work), where I would see account numbers and names, and the user can sort on either numbers or names an select the appropriate one. Any ideas?

 

[venkat1926]

Sheet database gives the information
In rolodex I have a combo box from the forms toolbar
I have transposed B1 C1 D1 from the database sheet to rolodex sheet at
E1 E2 E3
Right click the combo box and click “format control”. In that window put input range as E1:E3 and linked cell as B1
Now see the formula in B3
Type any name in A3. From combo box click any information (address,phone no. or account no.)
You get that information in B3

The only condition is the order of column heads in database sheet should not be changed.
I am sending the two sheets below. The combo box may not be seen. I hope you can park a combo box from toolabar as mentioned above.
Change to suit you. Confirm this is what you want.
If you want my file you have to send a personal email.
Venkat

vlookupl combobox.xls
	A	B	C	D	E	F
1	name	3			address
2					phoneno.
3	d	123			accoutno.
4
5
6
rolodex

vlookupl combobox.xls
	A	B	C	D
1	name	address	phoneno.	accoutno.
2	a	q	4	100
3	s	w	9	162
4	d	e	8	101
5	f	r	6	124
6	g	t	6	139
7	h	y	2	121
8	j	u	2	138
9	k	i	4	123
10	l	o	2	134
database

 

[Legacy 53668]

This is helpful for pulling the account number when you know the account name. However, sometimes the user knows the account number and sometimes they know the account name. I want to give them the option of pulling down the account number when they know the name, or vice versa. Any ideas?

 

[Legacy 53668]

This is helpful for pulling the account number when you know the account name. However, sometimes the user knows the account number and sometimes they know the account name. I want to give them the option of pulling down the account number when they know the name, or vice versa. Any ideas?

 

[venkat1926]

yes it an be done.
have another combo box iwth all the items name, etc.
only thing is we cannot use vlookup as the account number is not in the first coumn. we have to use index match functions. I shall get you that little later in the day.
venkat

 

[jindon]

Hi
You could have duplicate names in the list....
assuming Names in col.A & Acc# in Col.B of "database" sheet.

1) right click on the sheet tab of "rolodex" and select [ViewCode]
2) paste whole code onto the right pane and close the window
3) select other sheet once then return to "rolodex" sheet

The code populates in-cell drop down list in A2:B2 on "rolodex" sheet,
so you can select the item.
Hope it works..

Private a()

Private Sub Worksheet_Activate()
Dim dic As Object, i As Long, myNames, myAcc As String
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = vbTextCompare
a = Sheets("database").Range("a1").CurrentRegion.Value
For i = 2 To UBound(a,2)
   If Not IsEmpty(a(i,1)) And Not dic.exists(a(i,1)) Then dic.add a(i,1), Empty
   If Not IsEmpty(a(i,2)) Then myAcc = myAcc & a(i,2) & ","
Next
myNames = dic.keys : Set dic = Nothing
Application.EnableEvents = False
Range("a1").Resize(,2).Value = Array("Name","AccountNumber")
With Range("a2").Validation
   .Delete
   .Add Type:=xlValidationList, Formula1:=Join(myNames,",")
End With
With Range("b2").Validation
   .Delete
   .Add Type:=xlValidationList, Formula1:=Left(myAcc,Len(myAcc)-1)
End With
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, ii As Integer, x As Integer, r As Long
With Target
   If Intersect(Range("a2:b2"),.Cells) Is Nothing Then Exit Sub
   If .Value = "" Then Exit Sub
   Application.EnableEvents = False
   Range("a5").CurrentRegion.ClearContents
   r = 5
   If .Address(0,0) = "A2" Then 
      x = 1 : Range("b2").ClearContents
   Else
      x = 2 : Range("a2").ClearContents
   End If
   For i = 1 To UBound(a,1)
      If a(i,x) = .Value Then
         For ii = 1 To UBound(a,2)
            Cells(r,ii).Value = a(i,ii)
         Next
         r = r + 1
      End If
   Next
End With
Application.EnableEvents = True
End Sub

 

[venkat1926]

to HIMC
I have dispensed with combo box because yow wanted either account no for name or name for account number.
As my database sheet is available with you I am sending only my NEW rolodex sheet
See formula in B2
If you type the name in A2 you get account number in B2
If you type the accunt number in A2 you get the name in B2
In the sheet sent to you I have entered name “d” in A1 I got the ccount no. 121 in B2.
Now YOU enter 162 in A2 you must get “d” in B2

Conditions
1. The COLUMN configurations in database sheet should be the same
That is name should be in the first column and the account number should be in the fourth column
2. The account number should be in NUMBER and NOT text
if there is any bug let me know.

You keep my old rolodex file for getting either address, phone no. or account no. given the name. This NEW rolodex sheet is only for getting account no. for name or vice versa.
is this what you want?

Venkat

the rolodex sheet is gven below.

vlookupl combobox.xls
	A	B	C	D
1	name/ acctno.	accttno. orname
2	d	101
3
rolodex