Indexing question

[Jing]

So I have a range of cells AA7:AA58 has the following index formulas.

Cell - AA7: =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)))))))))))))
Cell - AA8: =IF(AE8=0,"",IF($AA$5=1,Jan!$C5,IF($AA$5=2,Feb!$C5,IF($AA$5=3,Mar!$C5,IF($AA$5=4,Apr!$C5,IF($AA$5=5,May!$C5,IF($AA$5=6,Jun!$C5,IF($AA$5=7,Jul!$C5,IF($AA$5=8,Aug!$C5,IF($AA$5=9,Sep!$C5,IF($AA$5=10,Oct!$C5,IF($AA$5=11,Nov!$C5,IF($AA$5=12,Dec!$C5)))))))))))))
Cell - AA9: =IF(AE9=0,"",IF($AA$5=1,Jan!$C6,IF($AA$5=2,Feb!$C6,IF($AA$5=3,Mar!$C6,IF($AA$5=4,Apr!$C6,IF($AA$5=5,May!$C6,IF($AA$5=6,Jun!$C6,IF($AA$5=7,Jul!$C6,IF($AA$5=8,Aug!$C6,IF($AA$5=9,Sep!$C6,IF($AA$5=10,Oct!$C6,IF($AA$5=11,Nov!$C6,IF($AA$5=12,Dec!$C6)))))))))))))

ETC...

Is there an easier way for say indexing or some other nested formula to accomplish this without having a 5 mile long set of if statements???

 

[Tom Urtis]

Those are some long formulas to comprehend for people not familiar with your workbook. Consider explaining in words what you are working with, data in what ranges, and what your expected results are.

 

[Jing]

Ok... i do understand that for sure.. they are quite long...

ok so i have updated things a bit since the original post...

Cell - AA7: =IF(AE7=0,"",IF($AA$5=1,AE7,IF($AA$5=2,AE7,IF($AA$5=3,AE7,IF($AA$5=4,AE7,IF($AA$5=5,AE7,IF($AA$5=6,AE7,IF($AA$5=7,AE7,IF($AA$5=8,AE7,IF($AA$5=9,AE7,IF($AA$5=10,AE7,IF($AA$5=11,AE7,IF($AA$5=12,AE7)))))))))))))
Cell - AA8: =IF(AE8=0,"",IF($AA$5=1,AE8,IF($AA$5=2,AE8,IF($AA$5=3,AE8,IF($AA$5=4,AE8,IF($AA$5=5,AE8,IF($AA$5=6,AE8,IF($AA$5=7,AE8,IF($AA$5=8,AE8,IF($AA$5=9,AE8,IF($AA$5=10,AE8,IF($AA$5=11,AE8,IF($AA$5=12,AE8)))))))))))))
Cell - AA9: =IF(AE9=0,"",IF($AA$5=1,AE9,IF($AA$5=2,AE9,IF($AA$5=3,AE9,IF($AA$5=4,AE9,IF($AA$5=5,AE9,IF($AA$5=6,AE9,IF($AA$5=7,AE9,IF($AA$5=8,AE9,IF($AA$5=9,AE9,IF($AA$5=10,AE9,IF($AA$5=11,AE9,IF($AA$5=12,AE9)))))))))))))

Here is the layout for the above formula's

Z -----------------------------AA-------- AB--------- AC
<TABLE style="WIDTH: 392pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=523><COLGROUP><COL style="WIDTH: 161pt; mso-width-source: userset; mso-width-alt: 7862" width=215><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5229" width=143><TBODY><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #376091; WIDTH: 161pt; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 height=21 width=215>Products</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #376091; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 width=81>Sales Made</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #376091; WIDTH: 63pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=84>Earned</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #376091; WIDTH: 107pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=143>Commission Stucture</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1; WIDTH: 161pt; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=21 width=215>Product 1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #95b3d7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>$ 1.50 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dbe5f1; WIDTH: 107pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=143>$1.50 </TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1; WIDTH: 161pt; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=21 width=215>Product 2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #95b3d7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>$ 4.50 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dbe5f1; WIDTH: 107pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=143>$1.50 </TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #dbe5f1; WIDTH: 161pt; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=21 width=215>Product 3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #95b3d7; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>$ 8.00 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dbe5f1; WIDTH: 107pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=143>$2.00 </TD></TR></TBODY></TABLE>

I have a combo box for the 12 months of the year. When you choose a month, say January, it will put a 1 into cell AA5, you choose February, and it will put a 2 into cell AA5, etc... depending on the month chosen it will pull the sales made from column AE... providing a view of the commission made for that month...

but having to create formulas so long it takes a long time to create these sheets, as i have to create a small if statement for every month of the year (for the specific product sold), and cram it all into one cell :P

Just looking for a way to shorten the formula's without resorting to VBA as the employee's here just want the ability to enter #'s and not have to think about other things :P

 

[Sandeep Warrier]

Your updated formula doesn't make much sense to me.

No matter what the value of AA5 is, AE7 is always returned. So you can just have

=IF(AE7=0,"",AE7)


For your earlier formula, if you could have a table like the one in D7:D18 below, you can have

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;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;">7</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">'Jan'!$C</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Feb'!$C</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Mar'!$C</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Apr'!$C</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">May'!$C</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Jun'!$C</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Jul'!$C</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Aug'!$C</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Sep'!$C</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Oct'!$C</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Nov'!$C</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Dec'!$C</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">A7</th><td style="text-align:left">=IF(<font color="Blue">AE7=0,"",INDIRECT(<font color="Red">LOOKUP(<font color="Green">$AA$5,{1,2,3,4,5,6,7,8,9,10,11,12},$D$7:$D$18</font>)&ROW(<font color="Green"></font>)-3</font>)</font>)</td></tr></tbody></table></td></tr></table><br />