Looking for names in a list and copying to random numbered cells

[katana_flyer]

Hello, trying to do this without vba if possible.

Sheet 1 contains
- a list of 15 names in column C
- a list of student number in column A

Sheet 2
- Has student’s numbers in cells scattered throughout the sheet in various cells. There are multiple cases of each students number on the sheet.

Object. As the list of names is keyed in on sheet 1, the corresponding student number on sheet 2 automatically changes to the students name.

I’m sure there are other threads, I’m just not sure what to look up as a rookie excel/computer guy.

Thanks for any help ,

 

[Peter_SSs]

If the cells in Sheet 2 have the student numbers simply typed into the various cells, then what you are asking is not possible without vba.

However, if instead of entering, say, the student number 44 in cell D5 of Sheet 2, you could put this formula.

=IFNA(VLOOKUP(44,Sheet1!$A$2:$C$1000,3,0),44)

If 44 does not yet exist in Sheet 1 then D5 in Sheet 2 will say 44.
Once 44 is entered in column A of Sheet 1 then D5 on Sheet 2 would automatically change to whatever is in column C of Sheet 1 on the row corresponding to the newly entered 44.

 

[katana_flyer]

If the cells in Sheet 2 have the student numbers simply typed into the various cells, then what you are asking is not possible without vba.

However, if instead of entering, say, the student number 44 in cell D5 of Sheet 2, you could put this formula.

=IFNA(VLOOKUP(44,Sheet1!$A$2:$C$1000,3,0),44)

If 44 does not yet exist in Sheet 1 then D5 in Sheet 2 will say 44.
Once 44 is entered in column A of Sheet 1 then D5 on Sheet 2 would automatically change to whatever is in column C of Sheet 1 on the row corresponding to the newly entered 44.

Thank you. I might be able to make that work. Alternatively, if I had to use vba, would it be easier? As of now, yes the student’s numbers have simply been entered in various cells around sheet 2.

 

[Peter_SSs]

if I had to use vba, would it be easier?

Yes. You could use this Worksheet_Change event code in Sheet1. To implement ..
1. Right click the Sheet 1 name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by entering data into Sheet1.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  Set Changed = Intersect(Target.EntireRow, Columns("A"))
  If Not Changed Is Nothing Then
    With Sheets("Sheet2").UsedRange
      For Each c In Changed
        If Not IsEmpty(c.Value) And Not IsEmpty(c.Offset(, 2).Value) Then .Replace What:=c.Value, Replacement:=c.Offset(, 2).Value, LookAt:=xlWhole
      Next c
    End With
  End If
End Sub

 

[Peter_SSs]

Note that I edited my previous post so make sure that you have refreshed the page so have the latest version.
And please test with a copy of your workbook.

 

[katana_flyer]

Note that I edited my previous post so make sure that you have refreshed the page so have the latest version.
And please test with a copy of your workbook.

Wonderful, am gonna give it a try. Thank you so much for your help.