Previous Topic Next Topic

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?

1 response

Answer 1 / 1
Trowa
Dec 7, 2009 at 08:20 AM
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
0

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response
Calculation of positions in excel
Mohsin -
Dausayi -

12 responses
formulas for 1st and 2nd position in class
jack -
suff954 -

1 response