Vlookup?

[Mrandall86]

I am attempting to creat a customer complaint log for work. I would like to use a search type function on a different sheet to search for the customers in the log but need to be able to return multiple results in the case that there are partial matches or similar names.

Also, with in doing that is there anyway to edit that information and have it save in the original data base? For instance, if I have a cell designated for the manager on duty to initial showing receipt of the credit is there a way to do that without goin to the original sheet and searching for the name doing it that way?

Lastly, is there an "enter" function that would be the oppsite of a lookup function where you can enter the information into a sheet and after hitting enter it will send that information to the complaint log?

Thanks for all the help

 

[al_b_cnu]

Hi and welcome to the board!

So you have 3 requirements here:
1) search complaint log on full/part name and return multiple results
2) Manager initials against a search result to be copied to the complaint log
3) Enter details and have this added to the complaint log

Quick answer: Yes it's all possible but you will require VBA.

1) FuzzyVLookup can achieve this (see link in my signature), the Rank parameter can control the multiple results requirement.
2) Can be achieved via aSheet Change event
3) A user defined form is likely to be the way to go here.

But without details, it's difficult to be more specific.

 

[Mrandall86]

Alan,

The information I am tracking is [Date of complaint, customer name, ph #, Complaint, Resolution, Manager on Duty, P/u Date, Manager on Duty2]. What I am hoping to do, and sorry if I simply repeat myself from last time, is have a one sheet that will contain all this information for ever complaint my company gets. I want to be able to search the data base by customer name and retrieve all the information about their credit.
So basically, if you called in to the about a replacement order, you could give me your name and i could search for it and find out what we owed you, then I could add my initials in the last box to signify that you had picked up your credit.
I am also wanting to be able to enter credits into the data base this way as well. My reasoning behind it is to eliminate confusion in entering the information in the data base and ease of finding customers names.

thanks again
Migael

 

[Aladin Akyurek]

Care to post an illustrative, fake sample along with the desired results?

 

[RonB1111]

You could simply turn auto filters on in you complaint log spreadsheet and users could use the customer filter to select all complaint records for that customer. They could then enter initials or edit as needed directly on the data displayed. I created this type of complaint log at our company and it worked fantastic - the only ramp up time required was showing a couple managers not familiar with Excel Auto Filters how to use them.

In case you're not familiar with auto filters here's a link:
http://www.contextures.com/xlautofilter01.html

 

[Mrandall86]

I found a tip on using VBA and figured out the code for the Entering of data. Now all i need is to figure out how to write the search code. I already have the cmd button built into my userform and the enter and close cmds work. just not sure how to do the code for search. I want to search the data base by 'txtname' and have it return 'txtdate' (which is in the col before txt name) 'txtphn', 'txtcomplaint', 'txtresolution', 'txtmod'.

I also need to be able to update the original data with and 'txtdate2' and 'txtmod2' if at all possible.

This is the code I have already for the 'entering' stage
Private Sub cmdadd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Customer List")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a customer name
If Trim(Me.txtname.Value) = "" Then
Me.txtname.SetFocus
MsgBox "Please enter a customer name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtdate.Value
ws.Cells(iRow, 2).Value = Me.txtname.Value
ws.Cells(iRow, 3).Value = Me.txtphn.Value
ws.Cells(iRow, 4).Value = Me.txtcomplaint.Value
ws.Cells(iRow, 5).Value = Me.txtresolution.Value
ws.Cells(iRow, 6).Value = Me.txtmod.Value
ws.Cells(iRow, 7).Value = Me.txtdate2.Value
ws.Cells(iRow, 8).Value = Me.txtmod2.Value




'clear the data
Me.txtdate.Value = ""
Me.txtname.Value = ""
Me.txtphn.Value = ""
Me.txtcomplaint.Value = ""
Me.txtresolution.Value = ""
Me.txtmod.Value = ""
Me.txtdate2.Value = ""
Me.txtmod2.Value = ""
Me.txtname.SetFocus