| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula prob - or is it validation?
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 |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formula prob - or is it validation?
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","")))) "suewho" wrote: 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 |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formula prob - or is it validation?
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". "JLatham" wrote: 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","")))) "suewho" wrote: 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 |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formula prob - or is it validation?
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. "suewho" wrote: 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". "JLatham" wrote: 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","")))) "suewho" wrote: 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 |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formula prob - or is it validation?
Yes, I have done that.
"JLatham" wrote: 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. "suewho" wrote: 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". "JLatham" wrote: 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","")))) "suewho" wrote: 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 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formula prob - or is it validation?
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! "JLatham" wrote: 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. "suewho" wrote: 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". "JLatham" wrote: 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","")))) "suewho" wrote: 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 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Formula prob - or is it validation?
Sue, you are very, very welcome. Thanks for letting us know you have it
working now. "suewho" wrote: 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! "JLatham" wrote: 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. "suewho" wrote: 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". "JLatham" wrote: 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","")))) "suewho" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CSE Formula Prob | Excel Worksheet Functions | |||
| vlookup prob with all #n/a's | Excel Discussion (Misc queries) | |||
| Macro Prob | Excel Worksheet Functions | |||
| Counting prob | Excel Discussion (Misc queries) | |||
| A little help, prob simple to anyone else | New Users to Excel | |||
Hybrid Mode
