INDEX OR INDIRECT
 

=IF(COUNTA(INDEX(F:L,ROWS($1:20)*5-97))=0,"",SUMIF(INDEX(F:L,ROWS($1:20)*5-97),"<0"))

The function above is what I want in cell T20. It works fine until I run one
of my marcos and the 20 in the index function changes. I have the index
because I didn't know how to make an indirect work for a logical test. Some
how I need the index to be like this: INDEX(F:L,ROWS($1:(CURRENT ROW)*5-97)).

Hopefully I explained this so someone can understand. Thanks for the help,
and if there is any confusion on my post let me know and I can try to
rephrase my question.

INDEX OR INDIRECT
 

This part :(CURRENT ROW)*5-97)).
is coded as ROW()*5-97
best wishes
--
www.stfx.ca/people/bliengme


"JBoyer" wrote:

=IF(COUNTA(INDEX(F:L,ROWS($1:20)*5-97))=0,"",SUMIF(INDEX(F:L,ROWS($1:20)*5-97),"<0"))

The function above is what I want in cell T20. It works fine until I run one
of my marcos and the 20 in the index function changes. I have the index
because I didn't know how to make an indirect work for a logical test. Some
how I need the index to be like this: INDEX(F:L,ROWS($1:(CURRENT ROW)*5-97)).

Hopefully I explained this so someone can understand. Thanks for the help,
and if there is any confusion on my post let me know and I can try to
rephrase my question.

INDEX OR INDIRECT
 

I tried that before, the problem is it is already going in a rows function,
ROWS($1:ROW()) It isn't letting me make a reference to a row inside a rows
function.

"Bernard Liengme" wrote:

This part :(CURRENT ROW)*5-97)).
is coded as ROW()*5-97
best wishes
--
www.stfx.ca/people/bliengme


"JBoyer" wrote:

=IF(COUNTA(INDEX(F:L,ROWS($1:20)*5-97))=0,"",SUMIF(INDEX(F:L,ROWS($1:20)*5-97),"<0"))

The function above is what I want in cell T20. It works fine until I run one
of my marcos and the 20 in the index function changes. I have the index
because I didn't know how to make an indirect work for a logical test. Some
how I need the index to be like this: INDEX(F:L,ROWS($1:(CURRENT ROW)*5-97)).

Hopefully I explained this so someone can understand. Thanks for the help,
and if there is any confusion on my post let me know and I can try to
rephrase my question.

INDEX OR INDIRECT
 

Have you tried my suggestion?
I am not sue what you hope to get with ROWS($1:Row())
--
www.stfx.ca/people/bliengme


"JBoyer" wrote:

I tried that before, the problem is it is already going in a rows function,
ROWS($1:ROW()) It isn't letting me make a reference to a row inside a rows
function.

"Bernard Liengme" wrote:

This part :(CURRENT ROW)*5-97)).
is coded as ROW()*5-97
best wishes
--
www.stfx.ca/people/bliengme


"JBoyer" wrote:

=IF(COUNTA(INDEX(F:L,ROWS($1:20)*5-97))=0,"",SUMIF(INDEX(F:L,ROWS($1:20)*5-97),"<0"))

The function above is what I want in cell T20. It works fine until I run one
of my marcos and the 20 in the index function changes. I have the index
because I didn't know how to make an indirect work for a logical test. Some
how I need the index to be like this: INDEX(F:L,ROWS($1:(CURRENT ROW)*5-97)).

Hopefully I explained this so someone can understand. Thanks for the help,
and if there is any confusion on my post let me know and I can try to
rephrase my question.

INDEX OR INDIRECT
 

I am aware that to get the current row you use ROW() , However I am not sure
how to put that into my already existing function, since it is not set up to
make a reference like that. This is my function..
=IF(COUNTA(INDEX(F:L,ROWS($1:20)*5-97))=0,"",SUMIF(INDEX(F:L,ROWS($1:20)*5-97),"<0"))
with you recommendation i would need to have a row function inside a rows
function, unless I don't understand what you mean.

"Bernard Liengme" wrote:

Have you tried my suggestion?
I am not sue what you hope to get with ROWS($1:Row())
--
www.stfx.ca/people/bliengme


"JBoyer" wrote:

I tried that before, the problem is it is already going in a rows function,
ROWS($1:ROW()) It isn't letting me make a reference to a row inside a rows
function.

"Bernard Liengme" wrote:

This part :(CURRENT ROW)*5-97)).
is coded as ROW()*5-97
best wishes
--
www.stfx.ca/people/bliengme


"JBoyer" wrote:

=IF(COUNTA(INDEX(F:L,ROWS($1:20)*5-97))=0,"",SUMIF(INDEX(F:L,ROWS($1:20)*5-97),"<0"))

The function above is what I want in cell T20. It works fine until I run one
of my marcos and the 20 in the index function changes. I have the index
because I didn't know how to make an indirect work for a logical test. Some
how I need the index to be like this: INDEX(F:L,ROWS($1:(CURRENT ROW)*5-97)).

Hopefully I explained this so someone can understand. Thanks for the help,
and if there is any confusion on my post let me know and I can try to
rephrase my question.

INDEX OR INDIRECT
 

Never mind, I dont need the $1, Only the ROW(). I didn't think my function
would work like that. I thought I need a range, not only one number. Thanks
for the suggestion.

"JBoyer" wrote:

I am aware that to get the current row you use ROW() , However I am not sure
how to put that into my already existing function, since it is not set up to
make a reference like that. This is my function..
=IF(COUNTA(INDEX(F:L,ROWS($1:20)*5-97))=0,"",SUMIF(INDEX(F:L,ROWS($1:20)*5-97),"<0"))
with you recommendation i would need to have a row function inside a rows
function, unless I don't understand what you mean.

"Bernard Liengme" wrote:

Have you tried my suggestion?
I am not sue what you hope to get with ROWS($1:Row())
--
www.stfx.ca/people/bliengme


"JBoyer" wrote:

I tried that before, the problem is it is already going in a rows function,
ROWS($1:ROW()) It isn't letting me make a reference to a row inside a rows
function.

"Bernard Liengme" wrote:

This part :(CURRENT ROW)*5-97)).
is coded as ROW()*5-97
best wishes
--
www.stfx.ca/people/bliengme


"JBoyer" wrote:

=IF(COUNTA(INDEX(F:L,ROWS($1:20)*5-97))=0,"",SUMIF(INDEX(F:L,ROWS($1:20)*5-97),"<0"))

The function above is what I want in cell T20. It works fine until I run one
of my marcos and the 20 in the index function changes. I have the index
because I didn't know how to make an indirect work for a logical test. Some
how I need the index to be like this: INDEX(F:L,ROWS($1:(CURRENT ROW)*5-97)).

Hopefully I explained this so someone can understand. Thanks for the help,
and if there is any confusion on my post let me know and I can try to
rephrase my question.

INDEX OR INDIRECT
 

If you are only looking for the last row in that set, you will need at least

=IF(COUNTA(INDEX(F:L,ROWS(A1:A20)*5-97,0))=0,"",SUMIF(INDEX(F:L,ROWS(A1:A20)*5-97,0),"<0"))

--
__________________________________
HTH

Bob

"JBoyer" wrote in message
...
Never mind, I dont need the $1, Only the ROW(). I didn't think my function
would work like that. I thought I need a range, not only one number.
Thanks
for the suggestion.

"JBoyer" wrote:

I am aware that to get the current row you use ROW() , However I am not
sure
how to put that into my already existing function, since it is not set up
to
make a reference like that. This is my function..
=IF(COUNTA(INDEX(F:L,ROWS($1:20)*5-97))=0,"",SUMIF(INDEX(F:L,ROWS($1:20)*5-97),"<0"))
with you recommendation i would need to have a row function inside a rows
function, unless I don't understand what you mean.

"Bernard Liengme" wrote:

Have you tried my suggestion?
I am not sue what you hope to get with ROWS($1:Row())
--
www.stfx.ca/people/bliengme


"JBoyer" wrote:

I tried that before, the problem is it is already going in a rows
function,
ROWS($1:ROW()) It isn't letting me make a reference to a row inside a
rows
function.

"Bernard Liengme" wrote:

This part :(CURRENT ROW)*5-97)).
is coded as ROW()*5-97
best wishes
--
www.stfx.ca/people/bliengme


"JBoyer" wrote:

=IF(COUNTA(INDEX(F:L,ROWS($1:20)*5-97))=0,"",SUMIF(INDEX(F:L,ROWS($1:20)*5-97),"<0"))

The function above is what I want in cell T20. It works fine
until I run one
of my marcos and the 20 in the index function changes. I have the
index
because I didn't know how to make an indirect work for a logical
test. Some
how I need the index to be like this: INDEX(F:L,ROWS($1:(CURRENT
ROW)*5-97)).

Hopefully I explained this so someone can understand. Thanks for
the help,
and if there is any confusion on my post let me know and I can
try to
rephrase my question.