Rolodex

L

Legacy 53668

Guest
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?
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
ABCDEF
1name3address
2phoneno.
3d123accoutno.
4
5
6
rolodex
vlookupl combobox.xls
ABCD
1nameaddressphoneno.accoutno.
2aq4100
3sw9162
4de8101
5fr6124
6gt6139
7hy2121
8ju2138
9ki4123
10lo2134
database
 
L

Legacy 53668

Guest
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?
 
L

Legacy 53668

Guest
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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..
Code:
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
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
ABCD
1name/ acctno.accttno. orname
2d101
3
rolodex
 

Forum statistics

Threads
1,136,345
Messages
5,675,220
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top