Working with cells Range("C4:C9")'
So: if C4 = "E" then return Sheets("Sheet2").Range("C5")
if C4 = "G" then return Sheets("Sheet2").Range("D5")

and so on with S and N.

Then the same for cell C5 except I will go to Sheet3("C5")for E, G, S, N.

If you can jump start me proper coding with C4 and C5 I believe I can get the rest of the cells, C6, C7, C8 & C9.

Where each cell will have a different sheet to return from.

Thanks,
Howard


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C4:C9")) Is Nothing Then Exit Sub

Select Case Target.Value

Target = "E"
Target.Offset(, 1) = Sheets("Sheet2").Range("C5")

Target = "G"
Target.Offset(, 1) = Sheets("Sheet2").Range("D5")

Target = "S"
Target.Offset(, 1) = Sheets("Sheet2").Range("E5")

Target = "N"
Target.Offset(, 1) = Sheets("Sheet2").Range("F5")

Target = ""

End Select

End Sub