Let say in A1 to A3 I input as "A, B and C" respectively.
What I wish to do is, Once I change the text in A1 to "C", then A3 will
change automatically to "A". Again, once I change the the A2 as "A", then A3
will be change to "B".

Kelvin,

If you 'put' a 'C' in A3 you will destroy the formula there, you can
enter letters in A1 and A2, and in A3 the formula

=IF(AND(A1="a",A2="b"),"c",IF(AND(A1="b",A2="a")," c",IF(A1="a","b",IF(A1="b","a",IF(A2="a","b","a")) )))


The formula does not detect invalid entries, testing only for valid
sets of A, B and C


Kelvin Lee Wrote:
Let say in A1 to A3 I input as "A, B and C" respectively.
What I wish to do is, Once I change the text in A1 to "C", then A3
will
change automatically to "A". Again, once I change the the A2 as "A",
then A3
will be change to "B".


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=474570

Kelvin,

1. Insert a new row above row 1 and enter the labels that you want in
that row. (Without any blanks) The row may then be hidden if you want.

2. Right-click on the sheet tab, remove all the code in the module,
copy and paste following code:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TC As Long
Dim SC As Long
Dim C As Long
Dim X As Long

Application.EnableEvents = False

If Target.Row = 2 Then
C = Application.CountA(Range("1:1"))
If Target.Column <= C Then
On Error GoTo ErrHand
SC = Application.Match(Target.Value, _
Range(Cells(1, 1), Cells(1, C)), 0)
On Error GoTo 0
TC = Target.Column

For X = 1 To C
Cells(2, TC).Value = Cells(1, SC).Value
TC = TC + 1
If TC C Then TC = 1
SC = SC + 1
If SC C Then SC = 1
Next X
End If
End If
Application.EnableEvents = True
Exit Sub

ErrHand:
Err.Clear
MsgBox "I don't recognise that label"
Application.EnableEvents = True

End Sub


3. Enter a label in cell A2 and the rest will be automatically populated.

4. Change any label in row 2 and the rest will change in the order youm
want.

5. If you enter a lable that is not contained in Row 1 then you will
receive a messagebox telling you that it is not recognised and only that
cell will change.

6. At any time you can add labels to Row 1 and then run the Macro again
by entering any Label into Row 2

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk
"Kelvin Lee" wrote in message
...
Let say in A1 to A3 I input as "A, B and C" respectively.
What I wish to do is, Once I change the text in A1 to "C", then A3 will
change automatically to "A". Again, once I change the the A2 as "A", then
A3
will be change to "B".