Automatically fill following cells depending on one cell.

[Dezzie]

Hello. I am creating an input area for another Excel user.

I would like A2:A10 to return the names of months in order, depending on
what month was in A1.

For example, if A1=January, then A2=February, A3=March, A4=April, etc.
But if A1=February, then A2=March, A3=April, A4=May, etc.

Is there a way to do this using IF and OR, or a simpler way?
As well, A1 is being chosen using a list. Does this complicate matters too
much, or can I leave it in? Thanks.
Thank you

 

[Gary''s Student]

Select cells A1 thru A10 and:

Format > Cells... > Number > Custom > mmmm

In A2 enter:
=DATE(YEAR(A1),MONTH(A1)+1,1) and copy down

In A1 enter any date.

 

[Kassie]

Firstly, if you only show months up to A10, that means you only show 10
months, not 12.

You have to create a list of months, somewhere over to the right in you
spreadsheet, and then create a range name for each month. Iow, if you put
January in GG1, then you have to click on GG1, click in the address bar, and
enter January again.With February in GG2, you would do the same, and so on
till you have your 12 months.

In A2, insert the following formula, and copy it down to A10
=IF(A1="December",OFFSET(INDIRECT(A1),-11,0),OFFSET(INDIRECT(A1),1,0))

--

HTH

Kassie

Replace xxx with hotmail

 

[Dezzie]

Thank you both Kassie and Gary's Student.

I've tried out both your ways and am very impressed.
I like using the IF function for this, but I am running into a problem. 11
out of the twelve months work. But when I input December, I get a #REF!
error. I think, but I don't know, that this is because of the [value_if_true]
part.
=IF(A1="December",
OFFSET(INDIRECT(A1),-11,0), <----Trouble?
OFFSET(INDIRECT(A1),1,0))

Any solutions come to mind? Since I'm not quite sure what it means, I don't
know how to fix it.

Thank you both for your help,

 

[Kassie]

As a matter of fact, Gary's idea is the better one! My idea was born out of
not using English in my work, hence not being able to use the MONTH functions!

Be that as it may, have you created your 12 range names, directly under each
other, starting with January and ending with December? And entering the
names of the months in the same order?
Only then will this solution work!

The TRUE part actually retrieves the value January from 11 rows up, when you
enter December
--

HTH

Kassie

Replace xxx with hotmail

Thank you both Kassie and Gary's Student.

I've tried out both your ways and am very impressed.
I like using the IF function for this, but I am running into a problem. 11
out of the twelve months work. But when I input December, I get a #REF!
error. I think, but I don't know, that this is because of the [value_if_true]
part.
=IF(A1="December",
OFFSET(INDIRECT(A1),-11,0), <----Trouble?
OFFSET(INDIRECT(A1),1,0))

Any solutions come to mind? Since I'm not quite sure what it means, I don't
know how to fix it.

Thank you both for your help,
-------------------------------

You have to create a list of months, somewhere over to the right in you
spreadsheet, and then create a range name for each month. Iow, if you put
January in GG1, then you have to click on GG1, click in the address bar, and
enter January again.With February in GG2, you would do the same, and so on
till you have your 12 months.

In A2, insert the following formula, and copy it down to A10
=IF(A1="December",OFFSET(INDIRECT(A1),-11,0),OFFSET(INDIRECT(A1),1,0))

 

[Dezzie]

Thanks Kassie for the prompt reply, and even teaching me a little more about
excel.

I'm using the IF method because I can't work out a way to use the list with
the DATE function. But that's okay, because I have everything settled using
the IF method. I really appreciate it, thank you!

 

[Kassie]

lesaure is all mine!
--

HTH

Kassie

Replace xxx with hotmail