Simplify if statement...

[Jing]

=IF(AE7=0,"",IF($AA$5=1,Jan!$C4,IF($AA$5=2,Feb!$C4,IF($AA$5=3,Mar!$C4,IF($AA$5=4,Apr!$C4,IF($AA$5=5,May!$C4,IF($AA$5=6,Jun!$C4,IF($AA$5=7,Jul!$C4,IF($AA$5=8,Aug!$C4,IF($AA$5=9,Sep!$C4,IF($AA$5=10,Oct!$C4,IF($AA$5=11,Nov!$C4,IF($AA$5=12,Dec!$C4)))))))))))))

Just wondering if there is a way to simplify the above statement...
it is currently being entered into AA7

 

[MrKowz]

Try:

=IF(AE7=0,"",INDIRECT(TEXT(DATE(YEAR(TODAY()),$AA$5,1),"mmm")&"!$C4"))

 

[Jing]

that almost works it finds the correct cell to be reported into Column AA7 but reports 0 in the cells instead of the actual # on say the Jan Tab... Same effect for Feb and Mar... ETC...
so example would be

so teh information on the Jan Tab in cell C4 it = 4 but is reporting 0...

 

[MrKowz]

It works fine on my end. Is it returning a proper value for the first one (C4)? Are you needing to drag the formula to get values from C5, C6, C7, etc?

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">mar</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">45</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet3</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">AA7</th><td style="text-align:left">=IF(<font color="Blue">AE7=0,"",INDIRECT(<font color="Red">TEXT(<font color="Green">DATE(<font color="Purple">YEAR(<font color="Teal">TODAY(<font color="#FF00FF"></font>)</font>),$AA$5,1</font>),"mmm"</font>)&"!$C4"</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">mar</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Mar</b></th></tr></td></thead></table><br /><br />

 

[Jing]

i got it... my fault on that one... decided to copy and paste instead of typing it in... LOL

it works great

thanks for the fast reply...

 

[isasa74]

hi guys, is there anyone who can help me with the below formula? I would like to reduce the number of nested if because I actually reached the limit and I cannot add any others if statement.

=IF(OR(A5=100032;A5=113571;A5=114164;LEFT(D5;4)="8FI7");"";IF(OR(D5="4RR0.000.00";D5="4RS0.000.00");6;IF(AND(LEFT(D5;3)<>"1JA";LEFT(D5;3)<>"8LB";LEFT(D5;3)<>"1RS";LEFT(D5;3)<>"1RG";LEFT(D5;3)<>"1RR";LEFT(D5;3)<>"8RR";LEFT(D5;3)<>"8RS";LEFT(D5;3)<>"1RH";B5="NL10";A6<>"NL11");3,7;IF(LEFT(D5;3)="8LB";4,9;IF(AND(OR(LEFT(D5;3)="1JA";LEFT(D5;3)="1RS";LEFT(D5;3)="1RR";LEFT(D5;3)="1RH";LEFT(D5;3)="1RG";LEFT(D5;3)="8RR";LEFT(D5;3)="8RS");B5="NL10";A6<>"NL11");4,5;" ")))))