Last Line of Data
 

I am using a Pivot Table. On the outside of the table, I have several
calculations going on. Two of which involve finding the last line of the
data and using that specific line in the range.....if my pivot goes to line
3901, then in one calculation i have the following:

=COUNTIF(B5:B3901,""), just basically counting the blanks in that column
another, with the help of this site, I'm using
=SUMPRODUCT((B5;B3901="")*(E5:E3901=1))

Because this is being set up as a template for regular monitoring, the data
will be refreshed periodically, so I'm trying to make this as easy as
possible. Right now, my instructions include "find the # of LAST row of
data, and put in place of 3901 in these two cell formula's".(because I will
save template with 3901 as the base)

Does anyone know of a different way to accomplish this that isn't a manual
entry?

As always, all comments are helpful and appreciated

Cathy

Last Line of Data
 

Can you expand the range to include the largest number that you think you'll
see.

It's a trade-off, though. Bigger ranges mean longer calculation times. But I
bet:

=SUMPRODUCT((B5;B9999="")*(E5:E9999=1))

wouldn't be too bad.

cware wrote:

I am using a Pivot Table. On the outside of the table, I have several
calculations going on. Two of which involve finding the last line of the
data and using that specific line in the range.....if my pivot goes to line
3901, then in one calculation i have the following:

=COUNTIF(B5:B3901,""), just basically counting the blanks in that column
another, with the help of this site, I'm using
=SUMPRODUCT((B5;B3901="")*(E5:E3901=1))

Because this is being set up as a template for regular monitoring, the data
will be refreshed periodically, so I'm trying to make this as easy as
possible. Right now, my instructions include "find the # of LAST row of
data, and put in place of 3901 in these two cell formula's".(because I will
save template with 3901 as the base)

Does anyone know of a different way to accomplish this that isn't a manual
entry?

As always, all comments are helpful and appreciated

Cathy

--

Dave Peterson