Indexing question

Jing

Active Member
Joined
Feb 11, 2011
Messages
288
So I have a range of cells AA7:AA58 has the following index formulas.

Code:
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???
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224
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

Active Member
Joined
Feb 11, 2011
Messages
288
Ok... i do understand that for sure.. they are quite long...

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

Code:
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
 
Last edited:

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
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 />
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top