Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I change the range?
Hi How does this formula really work? =IF(ISERROR(INDEX('13DBC'!$A$1:$W$999,SUMPRODUCT(S MALL(ROW('13DBC'!$10:$999)*('13DBC'!$C$10:$C$999< ""),ROW()-9+COUNTA('13DBC'!$C$10:$C$999)-COUNTIF('13DBC'!$C$10:$C$999,"0"))),COLUMN()-1)),"",INDEX('13DBC'!$A$1:$W$999,SUMPRODUCT(SMALL( ROW('1 3DBC'!$10:$999)*('13DBC'!$C$10:$C$999<""),ROW()-9+COUNTA('13DBC'!$C$10:$C$999)-COUNTIF('13DBC'!$C$10:$C$999,"0"))),COLUMN()-1)) I need to be able to manipulate the formula should I wish to reduce the range from say 999 to 500. I changing all the $999 to $500 and went to 13DBC worksheet, and deleted all the formulas from row 501 (this messed up the formula) I see there is some kind of blue line that surrounds '13DBC'! A09:S1001 It seems to be linked to make the INDEX formula work. I also tried reducing that, and it messed up my formula's What is the best procedure to reduce the range in the formula above and in the area range in 13DBC? -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=401659 |
#2
|
|||
|
|||
Best to start by breaking it up.
First create a name, InsertNameDefine..., of say cCount, with a RefersTo value of ='13DBC'!$C$10:$C$999. Then create another name of say cSum with a RefersTo value of =SUMPRODUCT(SMALL(ROW(cCount)*(cCount<""),ROW()-9+COUNTA(cCount)-COUNTIF(cC ount,"0"))) then your formula becomes =IF(ISERROR(INDEX('13DBC'!$A$1:$W$999,cSum,COLUMN( )-1)),"",INDEX('13DBC'!$A$ 1:$W$999,cSum,COLUMN()-1)) You now have a much simpler formula that you can change the ranges here and in the cCount name. -- HTH RP (remove nothere from the email address if mailing direct) "sonar" wrote in message ... Hi How does this formula really work? =IF(ISERROR(INDEX('13DBC'!$A$1:$W$999,SUMPRODUCT(S MALL(ROW('13DBC'!$10:$999)*('13DBC'!$C$10:$C$999< ""),ROW()-9+COUNTA('13DBC' !$C$10:$C$999)-COUNTIF('13DBC'!$C$10:$C$999,"0"))),COLUMN()-1)),"",INDEX('1 3DBC'!$A$1:$W$999,SUMPRODUCT(SMALL(ROW('1 3DBC'!$10:$999)*('13DBC'!$C$10:$C$999<""),ROW()-9+COUNTA('13DBC'!$C$10:$C$9 99)-COUNTIF('13DBC'!$C$10:$C$999,"0"))),COLUMN()-1)) I need to be able to manipulate the formula should I wish to reduce the range from say 999 to 500. I changing all the $999 to $500 and went to 13DBC worksheet, and deleted all the formulas from row 501 (this messed up the formula) I see there is some kind of blue line that surrounds '13DBC'! A09:S1001 It seems to be linked to make the INDEX formula work. I also tried reducing that, and it messed up my formula's What is the best procedure to reduce the range in the formula above and in the area range in 13DBC? -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=401659 |
#3
|
|||
|
|||
Thanks for the help I will look into that tonight. -- sonar ------------------------------------------------------------------------ sonar's Profile: http://www.excelforum.com/member.php...fo&userid=8424 View this thread: http://www.excelforum.com/showthread...hreadid=401659 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create range bar graph | Charts and Charting in Excel | |||
Cell Change Color - Need Help | New Users to Excel | |||
View and Cell Sum Range | Excel Discussion (Misc queries) | |||
HOW TO USE A FORMULA TO CHANGE CELL COLOR ACCORDING TO DATE RANGE. | Excel Worksheet Functions | |||
How do I change an Excel range of cells from relative to absolute. | Excel Worksheet Functions |