How do I change the range?

[sonar]

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?

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[sonar]

Thanks for the help

I will look into that tonight.

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Bob Phillips]

Best to start by breaking it up.

First create a name, Insert>Name>Define..., 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>