how to solve the following

[nader assaleh]

Hi,
i attached a file in which M4:M21 (purple colored) are cells that can only accept numbers that are available in B4:B51 (yellow colored).
cell M4 can accept any number or text B4:B51 let us say 11L
M5 can accept any number or text in B4:B51 except 11L and any number or text C4:H4 (yellow colored cells)
and same logic should be applied until all choices are finished.

many thanks

 

[Debraj]

Hi Nader,

Check this one.. YES : VBA, couse you have uploaded a XLSM file..
I

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Deb
With Application
  .EnableEvents = False
  .ScreenUpdating = False
End With

Dim PrimaryCheckRange As Range, ValidateRange As Range

Set PrimaryCheckRange = Range("B4:B51")
Set ValidateRange = Range("M5:M21")
If Target.Count > 1 Then
  Application.Undo
  MsgBox "Only One cell please"
  GoTo Deb
End If
If Not Intersect(ValidateRange, Target) Is Nothing And Target <> "" Then
  If Application.CountIf(PrimaryCheckRange, Target) = 0 Then
  Application.Undo
  MsgBox "Not Applicable"
  Else
  For Each cell In ValidateRange.SpecialCells(2, 3)
  If Target.Address <> cell.Address Then
  If Application.CountIf(PrimaryCheckRange.Offset(Application.Match(cell, PrimaryCheckRange, False) - 1, 1).Resize(1, 6), _
  Target) > 0 Then
  Application.Undo
  MsgBox "Not Applicable"
  GoTo Deb
  End If
  End If
  Next cell
  End If
End If

Raj:
With Application
  .EnableEvents = True
  .ScreenUpdating = True
End With
Exit Sub

Deb:
  Resume Raj
End Sub

PS: I have added two more validation.. One Number can be inputed only once. and One number at a time..

Let us know, if need to remove any of those validation..

 

[nader assaleh]

Hi Debraj
thank you very much it is working.
but since im novice in VBA can you please solve it by writing formulas also i am attaching a file with three different tables and based on value in cell AA3 ( A340) the appropriate table is chosen.