Based on data validation list value Formula

[raw_geek]

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!

[oeldere]

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

[raw_geek]

Please see the attached file

VB code or formula

Thanks!

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

[ChemistB]

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))

[oeldere]

Or with VLookup.

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

[raw_geek]

Yes, both are works, Thanks. However I have limitation on the spreadsheet and any VB code or simple Formula?

Thanks!

[ChemistB]

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.

Please Login or Register  to view this content.

Is that what you are looking for?