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