Excel formula

Question

Can anyone help?

I'm trying to figure out a formula for alternating between two values in alternating cells. I have a document with 12 different worksheets, it's a rota so has 12 months and 4 weeks on each worksheet. What I want to do is to label each week with a letter (A or B) and have a formula that will automatically populate the rest of the cells or change them if the value is changed.

For example, on the first week I want the first letter to be in cell G3 and the second on R3 etc. So if I input A into G3, R3 will automatically populate with B and if I reverse these the alternative happenes.

I hope that makes sense to someone.

Thanks

Answer 1

And usually AC3=C and AN3=D?

Right-click on the sheet tab
Choose "View Code"
Paste in the code below
Close the VBA editor
Write A,B,C or D into cell G3,R3,AC3 or AN3

Andreas.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Where As Range, Area As Range, This As Range
Dim Keys As Variant, Items As Variant
Dim i As Integer, j As Integer

'We want to populate this cells...
Set Where = Range("G3,R3,AC3,AN3")
'...dynamicliy with this items
Items = Array("A", "B", "C", "D")

'Check if one of the cells we're interested in is changed
If Target.Count > 1 Then Exit Sub
Set Target = Intersect(Target, Where)
If Target Is Nothing Then Exit Sub

'Compile the range into an array, that's easier for the code below
Keys = Array()
For Each Area In Where.Areas
For Each This In Area.Cells
ReDim Preserve Keys(0 To UBound(Keys) + 1)
Set Keys(UBound(Keys)) = This
Next
Next

'Find the first item to populate
For j = 0 To UBound(Items)
If StrComp(Target.Value, Items(j), vbTextCompare) = 0 Then Exit For
Next
'Found?
If j > UBound(Items) Then Exit Sub

'Find the first cell to populate
For i = 0 To UBound(Keys)
If Keys(i).Address = Target.Address Then Exit For
Next
'Found?
If i > UBound(Keys) Then Exit Sub

Application.EnableEvents = False
Do
'Populate the cell with this item
Keys(i).Value = Items(j)
'Next cell and item
i = i + 1
If i > UBound(Keys) Then i = 0
j = j + 1
If j > UBound(Items) Then j = 0
'loop till we are at the start
Loop Until Keys(i).Address = Target.Address
Application.EnableEvents = True
End Sub

Answer 2

Hi Adam，

Greetings! Thank you for posting to Microsoft Community.

Here is a formula for alternating between two values. You can replace the "B" ,"A" with reference cells Like $A$1.

If not this situation, share us the data and the expect result.

Hope it helps.

Best Regards,

Snow Lu