Formula that returns the "following value" of a dropdown list

[brunocinelli]

Hi all,

I'm facing the following problem:

in cell A1, there's a drop-down list, where only one of a list of values can be selected (January, February, March, [...], December).

I'd like to put a formula in cell B1, so that B1 = The following value in the list, in relation to the value selected in A1.

That is: if the user selects "March", for example, in the drop-down list in A1, cell B1 will automatically display the value "April".

How can I do this with a formula?

Many thanks in advance
Bruno

[Jonmo1]

Try

=TEXT((A1&1)+31,"mmmm")

[brunocinelli]

hmm... this doesn't work...
I'm not sure the "+31" makes sense here, since the values in the drop-down list are not necessarily recognized as months by Excel

(but thx anyway)

[Jonmo1]

So what values ARE in the dropdown?
My suggestion was based on your dropdown being the 12 months, January February March etc..

[brunocinelli]

It's the 12 months, in this case.
Don't know why it doesn't work then...

[Jonmo1]

Define "Doesn't work"

do you get an error? what error?
do you get the wrong result? What result DID you get, and what result were you expecting..and why?
What value was in A1 when this error or wrong result occurred?

[brunocinelli]

The drop-down list in A1 contains exactly the following values: {Jan; Feb; Mar; Apr; May; Jun; Jul; Aug; Sep; Oct; Nov; Dec}. And the error I get when inserting the exact formula you suggested (I only switched the comma for a semicolon) is "#VALUE!"

[Jonmo1]

I think I know what's going on...

If you're using a different regional setting..
If you enter a date in a cell, are months spelled January February March etc...
Or do you have some other set of names for the months ?


Also, is this the ultimate goal on months?, or might there be some other use for this as well?
Are the values manually typed in the data validation, or are they referring to a range on a sheet? What is that range?

[brunocinelli]

When I type a date, the months are spelled January, February, March, etc [English (US)]. The values are typed manually in the data validation.

In this case, the ultimate goal is just months... But it would be certainly useful to learn how to apply this generally for data validating lists...

Do you know of any ways to set the formula to B1= the following value in the list L {a, b, c, ..., }, in relation to the value in A1 ?

[Jonmo1]

If you months are the US english months, then I don't see why it's not working.
Can you post a sample book?


I can't think of anyway to do it strictly based off the data validation list.
Especially if that list is just manually typed in.

If you put that list in a range of cells, say D1:D12 or whatever, then in the datavalidation use List =$D$1:$D$12

Then probably something can be done based off the list in D1:D12