Hello Peter1973,
I'm not sure off hand how that would be done with formulas as they are not my
strong suit. It wouldn't surprise me if someone here could show how it's done
but it's hard for me to imagine it would be less complicated than a vba solution.
You say 'if it has to be a macro I will try to work around that' so here are some
pretty detailed instructions to do what I'm thinking.
This sample only deals with row 10 in columns A:Z as per your posted example.
If that's just an example then post back with the rows & columns you really
want it to work on and we can adjust it to suit.
1) Go into the vb editor (Press Alt+F11)
2) Show the Project Explorer window (Press Ctrl+R)
3) In the project explorer, locate the name of the workbook and right click it.
Choose Insert > Module.
4) Now type (or paste) this into the white area that is the new module you just inserted.
Public OldVal As String
5) Next, in the project explorer locate the sheet you're working with and double click it.
(This will put you into the sheet code module for that sheet.)
6) Copy & paste this code into that module.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'work only on single cell selections
If Target.Column > 26 Then Exit Sub 'work only on columns A:Z
If Target.Row <> 10 Then Exit Sub ' work only on row 10
OldVal = Target.Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub 'work only on single cell changes
If Target.Column > 26 Then Exit Sub 'work only on columns A:Z
If Target.Row <> 10 Then Exit Sub ' work only on row 10
Dim ThisCol As Integer, Col As Integer
ThisCol = Target.Column
If ThisCol < 26 Then
For Col = ThisCol To 26
If Cells(10, Col).Value = OldVal Then _
Cells(10, Col).Value = Target.Value
Next Col
End If
End Sub
7) Press Alt+Q to close the vbe and get back to your sheet to try it out.
Post back with any changes you'd like to make.