Based on data validation list value Formula

I have a data validation list on B3. My lists are 1st Qt., 2nd Qt., 3rd Qt., 4th Qt., 5th Qt., 6th Qt. in B3
and my answer is in B8


B8=IF (B3=”1st Qt.”, “2nd Qt.”, “”)
B8=IF (B3=”2nd Qt.”, “3nd Qt.”, “”)
B8=IF (B3=”3rd Qt.”, “4th Qt.”, “”)
B8=IF (B3=”4th Qt.”, “5th Qt.”, “”)
B8=IF (B3=”5th Qt.”, “6th Qt.”, “”)

I would like to get a one formula to B8. Maybe OR, AND

Please help.......

Thanks!

you get better help, if you show us your excel file.

Please see the attached file

VB code or formula

Thanks!

Originally Posted by oeldere

you get better help, if you show us your excel file.

Is your data validation pulling from a named range or a cell range. Let's say your quarters are in C1:C6
in B8
=INDEX(C1:C6, MATCH(B3,C1:C6,0)+1)
Make sense?

Update: =IF(ISERROR(INDEX(C1:C6, MATCH(B3,C1:C6,0)+1)),"", INDEX(C1:C6, MATCH(B3,C1:C6,0)+1))

Or with VLookup.

I made the data-validation with a named range (see b3).

You never did attach your file (Go Advanced> Manage Attachments) so hard to give a "simple formula" I think that both Oeldere's and my solution were simple formulas (depending on how your data is set up).
However, here is a VBA solution placed in sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Quarter As String
If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
Quarter = Target.Value
Select Case Quarter
Case "1st Qt."
Target.Offset(5, 0).Value = "2nd Qt."
Case "2nd Qt."
Target.Offset(5, 0).Value = "3rd Qt."
Case "3rd Qt."
Target.Offset(5, 0).Value = "4th Qt."
Case "4th Qt."
Target.Offset(5, 0).Value = "5th Qt."
Case "5th Qt."
Target.Offset(5, 0).Value = "6th Qt."
Case Else
Target.Offset(5, 0).Value = ""
End Select
End Sub

Is that what you are looking for?