Formula prob - or is it validation?

  • Thread starter Thread starter suewho
  • Start date Start date
S

suewho

In cell A1 I have created a list with options - 1st quarter results, 2nd
quarter results, 3rd quarter results, 4th quarter results.
Cells A2, A3 & A4 are for the months of the quarter.
If I select "1st quarter in A1", I want A2 to read "Jan", A3 to read "Feb",
& A4 to read "Mar".
If I select 2nd quarter in A1, I want A2 to read "Apr", A3 to read "May" &
A4 to read "Jun".....and so on thru the 3rd and 4th quarters.
I have investigated IF statements, but they don't work...

I'm so stuck. Is anyone able to help
 
Assuming that your list in A1 (created using data validation) has these
options/items:
1st Quarter
2nd Quarter
3rd Quarter
4th Quarter

Then these formulas should work
A2
=IF($A$1="1st Quarter","JAN",IF($A$1="2nd Quarter","APR",IF($A$1="3rd
Quarter","JUL",IF($A$1="4th Quarter","OCT",""))))
A3
=IF($A$1="1st Quarter","FEB",IF($A$1="2nd Quarter","MAY",IF($A$1="3rd
Quarter","AUG",IF($A$1="4th Quarter","NOV",""))))
A4
=IF($A$1="1st Quarter","MAR",IF($A$1="2nd Quarter","JUN",IF($A$1="3rd
Quarter","SEP",IF($A$1="4th Quarter","DEC",""))))
 
Ah - the absolute cell! I had the basic formula right but neglected to
"absolute" it. However, even absolute has not helped in this instance.... I
have a blank cell where I should have "Jan".
 
Where I have put things like "1st Quarter" or "4th Quarter" in the formulas I
put up, you must enter exactly what shows up in your list. If you have
"First Quarter" then the formula should say "First Quarter" or whatever is
showing up in cell A1 for each choice.
 
Yes, I have done that.

JLatham said:
Where I have put things like "1st Quarter" or "4th Quarter" in the formulas I
put up, you must enter exactly what shows up in your list. If you have
"First Quarter" then the formula should say "First Quarter" or whatever is
showing up in cell A1 for each choice.
Click to expand...
 
Ooops, I've just checked again....and my list says First Quarter....etc, and
my formula was 1st Quarter.
Problem solved! I've spent so long on this today, and you've fixed it in two
emails. Many Many MANY thanks!
 
Hmmm...

I don't see Roger's reply in the thread but I see it quoted in your reply.

Try this:

Use the ordinal number to define the quarters. As the source for the drop
down use these:

1st Q (or Quarter)
2nd Q
3rd Q
4th Q

Then enter this formula in A2 and copy down to A4:

=TEXT((LEFT(A$1)*3-(ROWS(A2:A$4)-1))*30,"mmmm")

This will return the long month name like January. If you want the short
month name like Jan change "mmmm" to "mmm".
 
Many thanks to all who've responded. It's great to know there are still
people out there willing to lend their expertise to learners like myself.
 
You're welcome!

--
Biff
Microsoft Excel MVP


suewho said:
Many thanks to all who've responded. It's great to know there are still
people out there willing to lend their expertise to learners like myself.
Click to expand...
 
Back
Top