Array Question

M

mnuri

New Member
Joined
May 10, 2010
Messages
9
I have this crazy long formula, but its really not that complicated, a lot of the same stuff repeated... The formula does a check for values in column B8:B32 and proceeds to look in another sheet and report back values. The problem is, the data in B8:B32 is dynamic and sometimes contain zeroes. Well anytime the formula encounters a zero, it produces a blank ("") for the entire formula, not just the cell thats blank. Okay...enough said, here is the formula:

=IF(ROWS(F$8:F8)>$C$34,"",INDEX('Mar10'!B$101:B$520,SMALL(IF(('Mar10'!$B$101:$B$520=$B$8)+('Mar10'!$B$101:$B$520=$B$9)+('Mar10'!$B$101:$B$520=$B$10)+('Mar10'!$B$101:$B$520=$B$11)+('Mar10'!$B$101:$B$520=$B$12)+('Mar10'!$B$101:$B$520=$B$13)+('Mar10'!$B$101:$B$520=$B$14)+('Mar10'!$B$101:$B$520=$B$15)+('Mar10'!$B$101:$B$520=$B$16)+('Mar10'!$B$101:$B$520=$B$17)+('Mar10'!$B$101:$B$520=$B$18)+('Mar10'!$B$101:$B$520=$B$19)+('Mar10'!$B$101:$B$520=$B$20)+('Mar10'!$B$101:$B$520=$B$21)+('Mar10'!$B$101:$B$520=$B$22)+('Mar10'!$B$101:$B$520=$B$23)+('Mar10'!$B$101:$B$520=$B$24)+('Mar10'!$B$101:$B$520=$B$25)+('Mar10'!$B$101:$B$520=$B$26)+('Mar10'!$B$101:$B$520=$B$27)+('Mar10'!$B$101:$B$520=$B$28)+('Mar10'!$B$101:$B$520=$B$29)+('Mar10'!$B$101:$B$520=$B$30)+('Mar10'!$B$101:$B$520=$B$31)+('Mar10'!$B$101:$B$520=$B$32),ROW('Mar10'!$D$101:$D$520)-ROW('Mar10'!$D$101)+1),ROWS(F$8:F8))))

Here is the data from column A8:B32, which is derived from another formula so its not a 'empty' cell :

<TABLE style="WIDTH: 208pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=277 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 160pt; mso-width-source: userset; mso-width-alt: 7789" width=213><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 48pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=64 height=20>1</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 160pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=213>name 1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>2</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">name 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>3</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">name 3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>4</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">name 4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>5</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">name 5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>6</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">name 6</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">name 7</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>8</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">name 8</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>9</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">name 9</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>10</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">name 10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>11</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">name 11</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>12</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">name 12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>13</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">name 13</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>14</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">name 14</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>15</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">name 15</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>16</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>17</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>18</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>19</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>20</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>21</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>22</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>23</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>24</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>25</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

I have the big array formula pasted into cells F8:F32. The problem is, because nothing exists in cell B23, it puts in a blank for all the cells the data is supposed to be applied to (F8:F32). Example: ('Mar10'!$B$101:$B$520=$B$23) << This argument and all arguments until $B$32 (the criteria im checking for) are messing up the array... if i remove those, the formula works. I dont want to remove those, because the data is dynamic and i could possibly have something in cell B23, or additional criteria added later at some point, which i want to autopopulate or anything beyond...

I've messed around with IF statements revolving around countblank, or something like this : =IF($B8:INDEX($B$8:$B$32,MATCH($A$32,1/($B$8:$B$32<>"")))=$B8,"True","False") , .... But those arent helping me or I'm not applying it right. Can someone help please?

Thank you,

Mussa
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
I figured out a solution for this... its sort of 'bootleg' but it gets the job done and i've been working on it forever, so i'm content. If anyone finds an alternative, please post... i would eventually like to make it better.

The fix i used was to make all the values that were blank (""), in cells B23:B32 of the example, actually a space (" "), and it works. Dont know why, but it does.

Please keep me updated if someone knows why my criteria in the array cant be blank.

Mussa
 
Upvote 0
What do you have in C34?

Here is a shorter version...

Control+shift+enter, not just enter, and copy down:
Code: 
=IF(ROWS(F$8:F8)>$C$34,"",INDEX('Mar10'!B$101:B$520,
    SMALL(IF(ISNUMBER(MATCH('Mar10'!$B$101:$B$520,$B$8:$B$32,0)),
      ROW('Mar10'!$D$101:$D$520)-ROW('Mar10'!$D$101)+1),ROWS(F$8:F8))))
 
Last edited:
Upvote 0
C34 is a simple Sum formula that looks like this: Sum(C8:C33), which for this example equals 7.

The Names in column B are searched for in sheet "Mar10" and summed up, and then in column c, the sum is displayed.

The formula in column C for finding the names in the adjacent column B is :

=IF(COUNTBLANK($B8)=1,"",IF($C$3="March",COUNTIF('Mar10'!B$101:B$520,$B8),IF($C$3="April",COUNTIF('Apr10'!B$101:B$520,$B8),IF($C$3="May",COUNTIF('May10'!B$101:B$520,$B8),IF($C$3="June",COUNTIF('Jun10'!B$101:B$520,$B8),IF($C$3="July",COUNTIF('Jul10'!B$101:B$520,$B8),IF($C$3="August",COUNTIF('Aug10'!B$101:B$520,$B8),IF($C$3="September",COUNTIF('Sep10'!B$101:B$520,$B8),IF($C$3="October",COUNTIF('Oct10'!B$101:B$520,$B8),IF($C$3="November",COUNTIF('Nov10'!B$101:B$520,$B8),IF($C$3="December",COUNTIF('Dec10'!B$101:B$520,$B8),IF($C$3="YTD",COUNTIF('Mar10'!B$101:B$520,$B8)+COUNTIF('Apr10'!B$101:B$520,$B8)+COUNTIF('May10'!B$101:B$520,$B8)+COUNTIF('Jun10'!B$101:B$520,$B8)+COUNTIF('Jul10'!B$101:B$520,$B8)+COUNTIF('Aug10'!B$101:B$520,$B8)+COUNTIF('Sep10'!B$101:B$520,$B8)+COUNTIF('Oct10'!B$101:B$520,$B8)+COUNTIF('Nov10'!B$101:B$520,$B8)+COUNTIF('Dec10'!B$101:B$520,$B8)))))))))))))


The formula in column B, to actually find the names is (based on a data validation menu) :

=IF(ROWS(B$8:B8)>COUNTA(INDIRECT(SUBSTITUTE($C$2,", ","__"))),"",INDEX(INDIRECT(SUBSTITUTE($C$2,", ","__")),ROWS(B$8:B8)))


Thanks for your help.
 
Upvote 0
mnuri said:
C34 is a simple Sum formula that looks like this: Sum(C8:C33), which for this example equals 7.

The Names in column B are searched for in sheet "Mar10" and summed up, and then in column c, the sum is displayed.

The formula in column C for finding the names in the adjacent column B is :

=IF(COUNTBLANK($B8)=1,"",IF($C$3="March",COUNTIF('Mar10'!B$101:B$520,$B8),IF($C$3="April",COUNTIF('Apr10'!B$101:B$520,$B8),IF($C$3="May",COUNTIF('May10'!B$101:B$520,$B8),IF($C$3="June",COUNTIF('Jun10'!B$101:B$520,$B8),IF($C$3="July",COUNTIF('Jul10'!B$101:B$520,$B8),IF($C$3="August",COUNTIF('Aug10'!B$101:B$520,$B8),IF($C$3="September",COUNTIF('Sep10'!B$101:B$520,$B8),IF($C$3="October",COUNTIF('Oct10'!B$101:B$520,$B8),IF($C$3="November",COUNTIF('Nov10'!B$101:B$520,$B8),IF($C$3="December",COUNTIF('Dec10'!B$101:B$520,$B8),IF($C$3="YTD",COUNTIF('Mar10'!B$101:B$520,$B8)+COUNTIF('Apr10'!B$101:B$520,$B8)+COUNTIF('May10'!B$101:B$520,$B8)+COUNTIF('Jun10'!B$101:B$520,$B8)+COUNTIF('Jul10'!B$101:B$520,$B8)+COUNTIF('Aug10'!B$101:B$520,$B8)+COUNTIF('Sep10'!B$101:B$520,$B8)+COUNTIF('Oct10'!B$101:B$520,$B8)+COUNTIF('Nov10'!B$101:B$520,$B8)+COUNTIF('Dec10'!B$101:B$520,$B8)))))))))))))


The formula in column B, to actually find the names is (based on a data validation menu) :

=IF(ROWS(B$8:B8)>COUNTA(INDIRECT(SUBSTITUTE($C$2,", ","__"))),"",INDEX(INDIRECT(SUBSTITUTE($C$2,", ","__")),ROWS(B$8:B8)))


Thanks for your help.
Click to expand...

See my edited post for the formula in F8...

The lengthy formula above can also be reduced... But your set up is not fully clear to me.
 
Upvote 0
wow, that worked...but i dont know why, can you explain please? that really simplifies things... my issue now is i'm limited in the number of characters i can put into cell F8, because of the formula i have... i'll only paste a short bit of it, but its much much longer. I'll paste it for month of March and April, but the same formula applies from March-December.

Basically what the formula is doing, is looking for the names in each sheet (different months) and giving me a sum of data for each sheet (depending on which month is selected from the data validation menu). The problem is i have 25 different sets of criteria. One way to shorten it is by using tables, but its still very time consuming. I'll let you take a look at it... (Kudos for fixing the other issue, thats amazing, and thank you)...

=IF($C$3=”March”,IF(ROWS(F$8:F8)>$C$34,"",INDEX('Mar10'!B$101:B$520,SMALL(IF((Table2[Agent]=$B$8)+(Table2[Agent]=$B$9)+(Table2[Agent]=$B$10)+(Table2[Agent]=$B$11)+(Table2[Agent]=$B$12)+(Table2[Agent]=$B$13)+(Table2[Agent]=$B$14)+(Table2[Agent]=$B$15)+(Table2[Agent]=$B$16)+(Table2[Agent]=$B$17)+(Table2[Agent]=$B$18)+(Table2[Agent]=$B$19)+(Table2[Agent]=$B$20)+(Table2[Agent]=$B$21)+(Table2[Agent]=$B$22)+(Table2[Agent]=$B$23)+(Table2[Agent]=$B$24)+(Table2[Agent]=$B$25)+(Table2[Agent]=$B$26)+(Table2[Agent]=$B$27)+(Table2[Agent]=$B$28)+(Table2[Agent]=$B$29)+(Table2[Agent]=$B$30)+(Table2[Agent]=$B$31)+(Table2[Agent]=$B$32),ROW('Mar10'!$D$101:$D$520)-ROW('Mar10'!$D$101)+1),ROWS(F$8:F8)))), IF($C$3=”April”,IF(ROWS(F$8:F8)>$C$34,"",INDEX('Apr10'!B$101:B$520,SMALL(IF(('Apr10'!$B$101:$B$520=$B$8)+('Apr10'!$B$101:$B$520=$B$9)+('Apr10'!$B$101:$B$520=$B$10)+('Apr10'!$B$101:$B$520=$B$11)+('Apr10'!$B$101:$B$520=$B$12)+('Apr10'!$B$101:$B$520=$B$13)+('Apr10'!$B$101:$B$520=$B$14)+('Apr10'!$B$101:$B$520=$B$15)+('Apr10'!$B$101:$B$520=$B$16)+('Apr10'!$B$101:$B$520=$B$17)+('Apr10'!$B$101:$B$520=$B$18)+('Apr10'!$B$101:$B$520=$B$19)+('Apr10'!$B$101:$B$520=$B$20)+('Apr10'!$B$101:$B$520=$B$21)+('Apr10'!$B$101:$B$520=$B$22)+('Apr10'!$B$101:$B$520=$B$23)+('Apr10'!$B$101:$B$520=$B$24)+('Apr10'!$B$101:$B$520=$B$25)+('Apr10'!$B$101:$B$520=$B$26)+('Apr10'!$B$101:$B$520=$B$27)+('Apr10'!$B$101:$B$520=$B$28)+('Apr10'!$B$101:$B$520=$B$29)+('Apr10'!$B$101:$B$520=$B$30)+('Apr10'!$B$101:$B$520=$B$31)+('Apr10'!$B$101:$B$520=$B$32),ROW('Apr10'!$D$101:$D$520)-ROW('Apr10'!$D$101)+1),ROWS(F$8:F8)))), IF($C$3=”May”,IF(ROWS(F$8:F8)>$C$34,"",INDEX('May10'!B$101:B$520,SMALL(IF(('May10'!$B$101:$B$520=$B$8)+('May10'!$B$101:$B$520=$B$9)+('May10'!$B$101:$B$520=$B$10)+.........................................


When i did this for march to december, i got an error that said i have exceeding the amount of characters in a formula... so i changed March to table reference, and im going to do the same for the rest of the months so i can save some space.... hopefully there is an alternative though :)

Can i just use : ISNUMBER(MATCH('Mar10'!$B$101:$B$520,$B$8:$B$32,0) ??. not familiar with that syntex... Again, thanks for your help

Mussa
 
Upvote 0
I ended up condensing the formula down to this... adding up months March to December on the YTD10 sheet...

=IF($C$3="March",IF(ROWS(F$8:F8)>$C$34,"",INDEX('Mar10'!B$101:B$520,SMALL(IF(ISNUMBER(MATCH('Mar10'!$B$101:$B$520,$B$8:$B$32,0)),ROW('Mar10'!$D$101:$D$520)-ROW('Mar10'!$D$101)+1),ROWS(F$8:F8)))), IF($C$3="April",IF(ROWS(F$8:F8)>$C$34,"",INDEX('Apr10'!B$101:B$520,SMALL(IF(ISNUMBER(MATCH('Apr10'!$B$101:$B$520,$B$8:$B$32,0)),ROW('Apr10'!$D$101:$D$520)-ROW('Apr10'!$D$101)+1),ROWS(F$8:F8)))), IF($C$3="May",IF(ROWS(F$8:F8)>$C$34,"",INDEX('May10'!B$101:B$520,SMALL(IF(ISNUMBER(MATCH('May10'!$B$101:$B$520,$B$8:$B$32,0)),ROW('May10'!$D$101:$D$520)-ROW('May10'!$D$101)+1),ROWS(F$8:F8)))), IF($C$3="June",IF(ROWS(F$8:F8)>$C$34,"",INDEX('Jun10'!B$101:B$520,SMALL(IF(ISNUMBER(MATCH('Jun10'!$B$101:$B$520,$B$8:$B$32,0)),ROW('Jun10'!$D$101:$D$520)-ROW('Jun10'!$D$101)+1),ROWS(F$8:F8)))), IF($C$3="July",IF(ROWS(F$8:F8)>$C$34,"",INDEX('Jul10'!B$101:B$520,SMALL(IF(ISNUMBER(MATCH('Jul10'!$B$101:$B$520,$B$8:$B$32,0)),ROW('Jul10'!$D$101:$D$520)-ROW('Jul10'!$D$101)+1),ROWS(F$8:F8)))), IF($C$3="August",IF(ROWS(F$8:F8)>$C$34,"",INDEX('Aug10'!B$101:B$520,SMALL(IF(ISNUMBER(MATCH('Aug10'!$B$101:$B$520,$B$8:$B$32,0)),ROW('Aug10'!$D$101:$D$520)-ROW('Aug10'!$D$101)+1),ROWS(F$8:F8)))), IF($C$3="September",IF(ROWS(F$8:F8)>$C$34,"",INDEX('Sep10'!B$101:B$520,SMALL(IF(ISNUMBER(MATCH('Sep10'!$B$101:$B$520,$B$8:$B$32,0)),ROW('Sep10'!$D$101:$D$520)-ROW('Sep10'!$D$101)+1),ROWS(F$8:F8)))), IF($C$3="October",IF(ROWS(F$8:F8)>$C$34,"",INDEX('Oct10'!B$101:B$520,SMALL(IF(ISNUMBER(MATCH('Oct10'!$B$101:$B$520,$B$8:$B$32,0)),ROW('Oct10'!$D$101:$D$520)-ROW('Oct10'!$D$101)+1),ROWS(F$8:F8)))), IF($C$3="November",IF(ROWS(F$8:F8)>$C$34,"",INDEX('Nov10'!B$101:B$520,SMALL(IF(ISNUMBER(MATCH('Nov10'!$B$101:$B$520,$B$8:$B$32,0)),ROW('Nov10'!$D$101:$D$520)-ROW('Nov10'!$D$101)+1),ROWS(F$8:F8)))), IF($C$3="December",IF(ROWS(F$8:F8)>$C$34,"",INDEX('Dec10'!B$101:B$520,SMALL(IF(ISNUMBER(MATCH('Dec10'!$B$101:$B$520,$B$8:$B$32,0)),ROW('Dec10'!$D$101:$D$520)-ROW('Dec10'!$D$101)+1),ROWS(F$8:F8)))), IF($C$3="YTD",IF(ROWS(F$8:F8)>$C$34,"",INDEX(YTD10!B$7:B$4206,SMALL(IF(ISNUMBER(MATCH(YTD10!$B$7:$B$4206,$B$8:$B$32,0)),ROW(YTD10!$D$7:$D$4206)-ROW(YTD10!$D$7)+1),ROWS(F$8:F8)))),””)))))))))))


And the other formula from column C, i shortened to the one below, since now i have a YTD10 sheet... Worked out better than adding up each sheet individually...

=IF(COUNTBLANK($B32)=1,"",IF($C$3="March",COUNTIF('Mar10'!B$101:B$520,$B32),IF($C$3="April",COUNTIF('Apr10'!B$101:B$520,$B32),IF($C$3="May",COUNTIF('May10'!B$101:B$520,$B32),IF($C$3="June",COUNTIF('Jun10'!B$101:B$520,$B32),IF($C$3="July",COUNTIF('Jul10'!B$101:B$520,$B32),IF($C$3="August",COUNTIF('Aug10'!B$101:B$520,$B32),IF($C$3="September",COUNTIF('Sep10'!B$101:B$520,$B32),IF($C$3="October",COUNTIF('Oct10'!B$101:B$520,$B32),IF($C$3="November",COUNTIF('Nov10'!B$101:B$520,$B32),IF($C$3="December",COUNTIF('Dec10'!B$101:B$520,$B32),IF($C$3="YTD",COUNTIF(YTD10!B$7:B$4206,$B32)))))))))))))

I'm sure i could condense it more, just not familiar with how to go about it.. if anyone can help, that would be appreciated..otherwise, thats my update incase anyone else was interested in the solution...

Mussa
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
554
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
505
mumps
mumps
Deepakchoudhary
  • Solved
RANK based on selling multiple items in different criteria (Excel 2016)
Replies
3
Views
146
hagia_sofia
hagia_sofia
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Forum statistics

Threads
1,223,632
Messages
6,173,467
Members
452,516
Latest member
archcalx

Share this page

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
Back
Top