Help with excel formula

Closed
Charlie - Dec 6, 2009 at 07:15 AM
Trowa - Dec 7, 2009 at 08:20 AM
Hello,

I want to change the month of year in a cell depending on a start month
for instance

I have a field with the start month in, eg. 1 for Start of FY which is Nov 09 or perhaps I type the year in Nov 09

Within same spreadsheet I have 12 columns and dpedning on what is in the seperate field I want it to automatically put the months in each of the 12 columns

Any ideas?

So if the start month in A1 is Nov then you want, B1 to be Nov, C1 = Dec, D1 = Jan ... M1 = Okt.

Since you can only nest 7 IF formulas in one formula you are forced to use VBA.

Insert this code in a module of your workbook and change the range to suite your situation:

```Sub test()
If Range("A1") = "Jan" Then Range("B1") = "Jan"
If Range("A1") = "Feb" Then Range("B1") = "Feb"
If Range("A1") = "Mar" Then Range("B1") = "Mar"
If Range("A1") = "Apr" Then Range("B1") = "Apr"
If Range("A1") = "May" Then Range("B1") = "May"
If Range("A1") = "Jun" Then Range("B1") = "Jun"
If Range("A1") = "Jul" Then Range("B1") = "Jul"
If Range("A1") = "Aug" Then Range("B1") = "Aug"
If Range("A1") = "Sep" Then Range("B1") = "Sep"
If Range("A1") = "Okt" Then Range("B1") = "Okt"
If Range("A1") = "Nov" Then Range("B1") = "Nov"
If Range("A1") = "Dec" Then Range("B1") = "Dec"

If Range("B1") = "Jan" Then Range("C1") = "Feb"
If Range("B1") = "Feb" Then Range("C1") = "Mar"
If Range("B1") = "Mar" Then Range("C1") = "Apr"
If Range("B1") = "Apr" Then Range("C1") = "May"
If Range("B1") = "May" Then Range("C1") = "Jun"
If Range("B1") = "Jun" Then Range("C1") = "Jul"
If Range("B1") = "Jul" Then Range("C1") = "Aug"
If Range("B1") = "Aug" Then Range("C1") = "Sep"
If Range("B1") = "Sep" Then Range("C1") = "Okt"
If Range("B1") = "Okt" Then Range("C1") = "Nov"
If Range("B1") = "Nov" Then Range("C1") = "Dec"
If Range("B1") = "Dec" Then Range("C1") = "Jan"

If Range("C1") = "Jan" Then Range("D1") = "Feb"
If Range("C1") = "Feb" Then Range("D1") = "Mar"
If Range("C1") = "Mar" Then Range("D1") = "Apr"
If Range("C1") = "Apr" Then Range("D1") = "May"
If Range("C1") = "May" Then Range("D1") = "Jun"
If Range("C1") = "Jun" Then Range("D1") = "Jul"
If Range("C1") = "Jul" Then Range("D1") = "Aug"
If Range("C1") = "Aug" Then Range("D1") = "Sep"
If Range("C1") = "Sep" Then Range("D1") = "Okt"
If Range("C1") = "Okt" Then Range("D1") = "Nov"
If Range("C1") = "Nov" Then Range("D1") = "Dec"
If Range("C1") = "Dec" Then Range("D1") = "Jan"

If Range("D1") = "Jan" Then Range("E1") = "Feb"
If Range("D1") = "Feb" Then Range("E1") = "Mar"
If Range("D1") = "Mar" Then Range("E1") = "Apr"
If Range("D1") = "Apr" Then Range("E1") = "May"
If Range("D1") = "May" Then Range("E1") = "Jun"
If Range("D1") = "Jun" Then Range("E1") = "Jul"
If Range("D1") = "Jul" Then Range("E1") = "Aug"
If Range("D1") = "Aug" Then Range("E1") = "Sep"
If Range("D1") = "Sep" Then Range("E1") = "Okt"
If Range("D1") = "Okt" Then Range("E1") = "Nov"
If Range("D1") = "Nov" Then Range("E1") = "Dec"
If Range("D1") = "Dec" Then Range("E1") = "Jan"

If Range("E1") = "Jan" Then Range("F1") = "Feb"
If Range("E1") = "Feb" Then Range("F1") = "Mar"
If Range("E1") = "Mar" Then Range("F1") = "Apr"
If Range("E1") = "Apr" Then Range("F1") = "May"
If Range("E1") = "May" Then Range("F1") = "Jun"
If Range("E1") = "Jun" Then Range("F1") = "Jul"
If Range("E1") = "Jul" Then Range("F1") = "Aug"
If Range("E1") = "Aug" Then Range("F1") = "Sep"
If Range("E1") = "Sep" Then Range("F1") = "Okt"
If Range("E1") = "Okt" Then Range("F1") = "Nov"
If Range("E1") = "Nov" Then Range("F1") = "Dec"
If Range("E1") = "Dec" Then Range("F1") = "Jan"

If Range("F1") = "Jan" Then Range("G1") = "Feb"
If Range("F1") = "Feb" Then Range("G1") = "Mar"
If Range("F1") = "Mar" Then Range("G1") = "Apr"
If Range("F1") = "Apr" Then Range("G1") = "May"
If Range("F1") = "May" Then Range("G1") = "Jun"
If Range("F1") = "Jun" Then Range("G1") = "Jul"
If Range("F1") = "Jul" Then Range("G1") = "Aug"
If Range("F1") = "Aug" Then Range("G1") = "Sep"
If Range("F1") = "Sep" Then Range("G1") = "Okt"
If Range("F1") = "Okt" Then Range("G1") = "Nov"
If Range("F1") = "Nov" Then Range("G1") = "Dec"
If Range("F1") = "Dec" Then Range("G1") = "Jan"

If Range("G1") = "Jan" Then Range("H1") = "Feb"
If Range("G1") = "Feb" Then Range("H1") = "Mar"
If Range("G1") = "Mar" Then Range("H1") = "Apr"
If Range("G1") = "Apr" Then Range("H1") = "May"
If Range("G1") = "May" Then Range("H1") = "Jun"
If Range("G1") = "Jun" Then Range("H1") = "Jul"
If Range("G1") = "Jul" Then Range("H1") = "Aug"
If Range("G1") = "Aug" Then Range("H1") = "Sep"
If Range("G1") = "Sep" Then Range("H1") = "Okt"
If Range("G1") = "Okt" Then Range("H1") = "Nov"
If Range("G1") = "Nov" Then Range("H1") = "Dec"
If Range("G1") = "Dec" Then Range("H1") = "Jan"

If Range("H1") = "Jan" Then Range("I1") = "Feb"
If Range("H1") = "Feb" Then Range("I1") = "Mar"
If Range("H1") = "Mar" Then Range("I1") = "Apr"
If Range("H1") = "Apr" Then Range("I1") = "May"
If Range("H1") = "May" Then Range("I1") = "Jun"
If Range("H1") = "Jun" Then Range("I1") = "Jul"
If Range("H1") = "Jul" Then Range("I1") = "Aug"
If Range("H1") = "Aug" Then Range("I1") = "Sep"
If Range("H1") = "Sep" Then Range("I1") = "Okt"
If Range("H1") = "Okt" Then Range("I1") = "Nov"
If Range("H1") = "Nov" Then Range("I1") = "Dec"
If Range("H1") = "Dec" Then Range("I1") = "Jan"

If Range("I1") = "Jan" Then Range("J1") = "Feb"
If Range("I1") = "Feb" Then Range("J1") = "Mar"
If Range("I1") = "Mar" Then Range("J1") = "Apr"
If Range("I1") = "Apr" Then Range("J1") = "May"
If Range("I1") = "May" Then Range("J1") = "Jun"
If Range("I1") = "Jun" Then Range("J1") = "Jul"
If Range("I1") = "Jul" Then Range("J1") = "Aug"
If Range("I1") = "Aug" Then Range("J1") = "Sep"
If Range("I1") = "Sep" Then Range("J1") = "Okt"
If Range("I1") = "Okt" Then Range("J1") = "Nov"
If Range("I1") = "Nov" Then Range("J1") = "Dec"
If Range("I1") = "Dec" Then Range("J1") = "Jan"

If Range("J1") = "Jan" Then Range("K1") = "Feb"
If Range("J1") = "Feb" Then Range("K1") = "Mar"
If Range("J1") = "Mar" Then Range("K1") = "Apr"
If Range("J1") = "Apr" Then Range("K1") = "May"
If Range("J1") = "May" Then Range("K1") = "Jun"
If Range("J1") = "Jun" Then Range("K1") = "Jul"
If Range("J1") = "Jul" Then Range("K1") = "Aug"
If Range("J1") = "Aug" Then Range("K1") = "Sep"
If Range("J1") = "Sep" Then Range("K1") = "Okt"
If Range("J1") = "Okt" Then Range("K1") = "Nov"
If Range("J1") = "Nov" Then Range("K1") = "Dec"
If Range("J1") = "Dec" Then Range("K1") = "Jan"

If Range("K1") = "Jan" Then Range("L1") = "Feb"
If Range("K1") = "Feb" Then Range("L1") = "Mar"
If Range("K1") = "Mar" Then Range("L1") = "Apr"
If Range("K1") = "Apr" Then Range("L1") = "May"
If Range("K1") = "May" Then Range("L1") = "Jun"
If Range("K1") = "Jun" Then Range("L1") = "Jul"
If Range("K1") = "Jul" Then Range("L1") = "Aug"
If Range("K1") = "Aug" Then Range("L1") = "Sep"
If Range("K1") = "Sep" Then Range("L1") = "Okt"
If Range("K1") = "Okt" Then Range("L1") = "Nov"
If Range("K1") = "Nov" Then Range("L1") = "Dec"
If Range("K1") = "Dec" Then Range("L1") = "Jan"

If Range("L1") = "Jan" Then Range("M1") = "Feb"
If Range("L1") = "Feb" Then Range("M1") = "Mar"
If Range("L1") = "Mar" Then Range("M1") = "Apr"
If Range("L1") = "Apr" Then Range("M1") = "May"
If Range("L1") = "May" Then Range("M1") = "Jun"
If Range("L1") = "Jun" Then Range("M1") = "Jul"
If Range("L1") = "Jul" Then Range("M1") = "Aug"
If Range("L1") = "Aug" Then Range("M1") = "Sep"
If Range("L1") = "Sep" Then Range("M1") = "Okt"
If Range("L1") = "Okt" Then Range("M1") = "Nov"
If Range("L1") = "Nov" Then Range("M1") = "Dec"
If Range("L1") = "Dec" Then Range("M1") = "Jan"
End Sub```

Best regards,
Trowa