Dear Roger,

Thank you for your help...it works really well..I'm going to try and adapt
the code and apply it to my assessment file!

Thanks again,

Matt Bowen - Infant teacher, UK

"Roger Govier" wrote:

Hi Matt

I had not even looked to see if there was any VBA in the workbook - I was
just giving a formula solution.
I have uploaded a new version of the book with revised event code at
http://www.box.net/shared/iuudnyqz1g

The revised code is
Private Sub Worksheet_Change(ByVal Target As Range)
Dim name As String, group As String
Dim wsg As Worksheet, wss As Worksheet, tr As Long, tc As Long

Set wsg = ThisWorkbook.Sheets("APP Groups")
Set wss = ThisWorkbook.Sheets("APP Sheets")
tc = Target.Column

Select Case tc

Case 2, 5

If Not Intersect(Target, Range("names_range")) Is Nothing Then
name = Target.Value
Application.EnableEvents = False

If WorksheetFunction.CountIf(Range("B3:E5"), name) 1 Then
MsgBox "You have entered name " & name & " more than once"
Target.Value = ""
GoTo exit_Sub
End If

End If
GoTo exit_Sub

Case 3, 6

If Not Intersect(Target, Range("names_range").Offset(0, 1)) Is
Nothing Then
name = Target.Offset(0, -1)
Application.EnableEvents = False
name = Target.Offset(0, -1)
group = Target.Value
On Error Resume Next
tr = Application.Match(name, wss.Range("C:C"), 0)
On Error GoTo 0
If tr 0 Then
wss.Cells(tr, "D") = group
Else
tr = wss.Cells(Rows.Count, 3).End(xlUp).Row + 2
wss.Cells(tr, "C") = name
wss.Cells(tr, "D") = group
End If
End If

Case Else
End Select
exit_Sub:
Application.EnableEvents = True
End Sub

The reason why you had to select a name again, before their group was
updating, was because you were only applying event code to the Name entry,
not the Group entry.
I created a new dynamic range range called Names in column A of APP Sheets
(hide the column if you wish, or move it elsewhere) so you can add any
number of new Names that you wish.

Equally, you can add those new names in column C and the code will enter a
group for them in column D.
If the new name has not been added in column C, then the code will generate
a new pair of entries 2 rows below your last entry on the sheet.


--
Regards
Roger Govier

"mj_bowen" wrote in message
...
Hi Roger,

Thank you for the message, however, I need the drop down menu in the cells
in column C and F on 'Sheet APP' groups. It is the data in APP groups that
is
entered into APP sheets!

For example if Bernie is in 'Group one' on the APP group tab
his score may be 2a (which is fed into cell D15 on the APP sheet tab). The
next day he may be in Group 3 and have the score of 1a, which updates onto
cell D15 on the APP sheet tab.

this would also apply to the other names in the file!

Any advice would be warmly welcomed!

Here is an updated version!
http://www.box.net/shared/hxr0isxpfi

regards,
Matt

"Roger Govier" wrote:

Hi

Remove Data Validation from column C and F on Sheet APP Groups
Use the following formula in cell C3
=IF(B3="","",VLOOKUP(B3,'APP Sheets'!C:D,2,0))

Copy as appropriate to other cells as required.

--
Regards
Roger Govier

"mj_bowen" wrote in message
...
Hi,

Basically if any of the names is selected in any of the drop down
cells,
then the score that is next to that cell eg, 1a,2b, 3c, 4c is shown
next
to
the appropriate name in the APP sheets tab!

I have attached a copy of the worbook;
http://www.box.net/shared/hxr0isxpfi

I've tried all sorts but can't seem make it work!

Matt

This thread is also posted on
http://www.mrexcel.com/forum/showthr...=1#post2202650

__________ Information from ESET Smart Security, version of virus
signature database 4832 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus
signature database 4832 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus
signature database 4833 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4833 (20100203) __________

The message was checked by ESET Smart Security.

http://www.eset.com