data validation/combo box for long list of items

Leslon

New Member
Joined
Jan 18, 2010
Messages
1
Hello,
I've been searching the web and this forum and I still haven't found what I'm looking for. Here is my situation:

I am making a client tracking spreadsheet that needs to have exact client names in order to link to other spreadsheets. The first thing that came to mind was to use data validation. The only problem is that we have 500 clients and scrolling down is a pain considering I have to use this cell about 500 times. Contrary to some posts I've read, I am not able to type in a letter which will jump me down the data validation list. Also, our client names are far to long and complicated for AutoComplete to fill them in so with data validation, it seems I have to type the whole name exactly correct or use the 500 name scroll down.

So then I read up on combo boxes which will narrow down your choices as you type. That's exactly what I need and it would save me a ton of time. What I don't like about these, however, is that they float as objects. I need them to be in a cell so I can drag copy it to other cells. Other information on this spreadsheet will be exported based on this client name so it's very important that it read like a regular cell. For instance, the client name might be used in a VLOOKUP function.

Is there a way to modify a cell to act like a combo box or embed the combo box into a cell? Is there another way to achieve my goal that I am overlooking?

http://www.mrexcel.com/archive/Data/13157.html I ran across this post and I want whatever Paul E. is talking about. Hope it helps.

I am using Excel 2007.

Thanks in advance,
Leslon
 
Last edited:

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

dhosi439

Board Regular
Joined
May 13, 2009
Messages
62
I think you might benefit by using a userform, here is some code from one of my userforms I use to enter data into a table I use as a ledger:

Code:
'cmdAdd is a command button
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet

Set ws = Worksheets("Checking")


'find first empty row in database
'This is used to determine if this is the first entry in the table or not
'Check box is selected manually for the first entry, left unchecked for every
'entry after the first
If cbxFirst.Value = "True" Then
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Row
Else
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
End If

'copy the data to the database
ws.Cells(iRow, 2).Value = Me.txtDate.Value
ws.Cells(iRow, 3).Value = Me.cbVenue.Value
ws.Cells(iRow, 4).Value = Me.txtDesc.Value
ws.Cells(iRow, 5).Value = Me.txtType.Value
ws.Cells(iRow, 6).Value = Me.txtDepo.Value
ws.Cells(iRow, 7).Value = Me.txtWith.Value
ws.Cells(iRow, 9).Value = Me.txtVeri.Value

'clear the data from userform
Me.txtDate.Value = ""
Me.cbVenue.Value = ""
Me.cbDesc.Value = ""
Me.cbType.Value = ""
Me.txtDepo.Value = ""
Me.txtWith.Value = ""
Me.cbVeri.Value = ""

'Workbook Refresh
ActiveWorkbook.RefreshAll

'Refresh Venue to see new data entries
frmMain.cbVenue.RowSource = "Venue"

Venue is a named range I created based on the Venue column in my table.

This will obviously need tweaking for your needs.

Here are some links if you need guidance for making user forms:

http://www.contextures.com/xlUserForm01.html

http://msdn.microsoft.com/en-us/library/bb149075.aspx

If this isn't what you're looking for I apologize, this is how I prefer to do data entry over data validation lists and putting multitudes of objects on a worksheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,549
Messages
5,625,443
Members
416,106
Latest member
Geo0

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