Last Line of Data

  • Thread starter Thread starter cware
  • Start date Start date
C

cware

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
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA - retrieve data on last create named range from previous inserted named range 0
How do I save Pivot Table Data 4
Pivot Table and adding a % column, that is not in original data 3
pivot tables - number of columny but need a column for each month 2
Would Like Pivot Table Data on 1 Row instead of Stacked Column 1
Mystery field in pivot table 8
Pivot table - multiple data sources 2
Countif or sumif for cumulative sums,pivot table & chart 2

Back
Top