Validating values of a column in a file from another file

[acsishere]

Dear Friends & Gurus,

I have some 50-60 files in which data entry is done by individual users. I have a shared workbook (LISTS.XLS) - (sheet named ADMIN) - in which the data already available as follows:

colA / ColB
(Branch) / (Code)
IVA / 5587
IVA / 8788
VC / 6783
VC / 7329
VC / 9283
VB / 2638
IVA / 4792
IVA / 6879
..... ColB consist unique values. There is no repitition.

I have a set of files which are named after the values of the colA of Lists.xls - (i.e. I have files like VB.XLS, IVA.XLS, VC.XLS, and so on).

Now I am to enter data in a file named IVA. It has some 80-90 columns. Among these, first I am to enter the CODE. When I am entering the code in colA) in this file, it should check:

1) In lists.xls file (in sheet named Admin) whether the ColA is equivalent to the file name of dataentry is being done.
2) If the file name is right, then it should check whether the entered value is there in colB of Admin sheet of lists.xls.

I attached a ZIP file, in which 3 files are there, and explained the requirement.

I hope, someone will come forward to help me to find out the solution.

The help is highly appreciated.

acsishere.

[Aussiebear]

While you are waiting for a responce from the Guru's, please look up the following items in Excel's help ISError, Index, & Match. Using a combination of these may be what you are looking for.

[acsishere]

Dear Sir,

Yes, I can try for that. But, I need to initiate a macro whenever the files are opened. Besides, the number of files are also more. If it could be a macro, then it will be easier for me to copy and initiate it at the time of opening the file. And if it is formula based, then every time the rows with formulas are to be dragged.

Hence, I felt that a macro will be the easier and efficient way of tackling this problem.

Thanks for the help in advance.

acsishere.

[acsishere]

Dear Friends,

Any help is highly appreaciated.

acsishere.

[acsishere]

Dear Friends,

I am very much in need of the solution.

Thanks in advance.

acsishere.

[acsishere]

I am very much in need of a help?

Any one please??

Thanks in advance.

acsishere.

[acsishere]

Dear Friends,

Any problem in my explanation or Is it not possible to do?

I hope someone will come forward to help me.

Thanks in advance.

acsishere.

[mdmackillop]

[VBA]Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim WB As Workbook, WS As Worksheet
Dim Branch As String, ThsBk As String
Dim c As Range, Lists As String
Lists = "C:\BBB\Lists.xls" '<--- Change to suit

If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

Application.ScreenUpdating = False
ThsBk = Split(ActiveWorkbook.Name, ".")(0)
Set WB = Workbooks.Open(Lists, , True)
Set WS = WB.Sheets("Admin")
Set c = WS.Columns(2).Find(Target)
If Not c Is Nothing Then
Branch = c.Offset(, -1)
If ThsBk = c.Offset(, -1) Then
Target.Offset(, 1) = "OK"
Else
Target.Offset(, 1) = "It is of " & Branch & " branch."
End If
Else
Target.Offset(, 1) = "The value is wrong."
End If
WB.Close
Application.ScreenUpdating = True
End Sub
[/VBA]

[acsishere]

Thanks a lot for your code. It works great.

Sir, can you please help me to do few enhancements.

1) In macro, Messages in next column are shown like this:

Target.Offset(, 1) = "It is of " & Branch & " branch."

INSTEAD, all messages are to be shown in a message box to convey the concerned message. If the code is not available/or of another branch, the same cell should be activated in which the data entry is done. OR, If the data is correct or OK then the next column (of same row) should be selected.

2) In the sheet where we enter the data, if the code is already exist in the same sheet, then it should say in a MsgBox that the code is already entered in this sheet.

I seek your kind guidance for the same.

Thanks in advance.

acsishere.

[acsishere]

Please help me to find out the solution.

Thanks a lot in advance.

acsishere.

[acsishere]

Can any one please help me to find the solution?

Thanking you and anticipating your expertise,

acsishere.

[lucas]

http://www.vbaexpress.com/consulting/