lastrow in formula
 

Hi everyone. I have a summary block on the top of a worksheet. Below
that is data, which is VBA populated and therefore every time the code
is run the amount of rows of data changes. How can I have a cell in
my Summary section do a direct cell reference on the last row? For
example, when I run the code, the last row of data is in row 45. So
the summary cell wold reference C45. If the last row was on 75, the
summary would reference C75. Thanks!

lastrow in formula
 

Hi

You don't tell us whether were looking for text or numeric so

Numeric
=ROW(INDEX(B:B,MATCH(9.99999999999999E+307,B:B)))
text
=ROW(INDEX(B:B,MATCH(REPT("z",10),B:B,1),1))

Both of which are arrays so commit with Ctrl+Shift+enter


Mike

"Steve" wrote:

Hi everyone. I have a summary block on the top of a worksheet. Below
that is data, which is VBA populated and therefore every time the code
is run the amount of rows of data changes. How can I have a cell in
my Summary section do a direct cell reference on the last row? For
example, when I run the code, the last row of data is in row 45. So
the summary cell wold reference C45. If the last row was on 75, the
summary would reference C75. Thanks!

lastrow in formula
 

On Wed, 26 Mar 2008 13:50:33 -0700 (PDT), Steve
wrote:

Hi everyone. I have a summary block on the top of a worksheet. Below
that is data, which is VBA populated and therefore every time the code
is run the amount of rows of data changes. How can I have a cell in
my Summary section do a direct cell reference on the last row? For
example, when I run the code, the last row of data is in row 45. So
the summary cell wold reference C45. If the last row was on 75, the
summary would reference C75. Thanks!

Do you want the contents of the cell in the last row of data in column C?

If so, you could use this formula:

=LOOKUP(2,1/(LEN(C1:C65535)0),C1:C65535)

If you want something else, please be more specific about your data layout and
exactly what you want returned.
--ron

lastrow in formula
 

On Mar 26, 2:50*pm, Steve wrote:
Hi everyone. *I have a summary block on the top of a worksheet. *Below
that is data, which is VBA populated and therefore every time the code
is run the amount of rows of data changes. *How can I have a cell in
my Summary section do a direct cell reference on the last row? *For
example, when I run the code, the last row of data is in row 45. *So
the summary cell wold reference C45. *If the last row was on 75, the
summary would reference C75. *Thanks!

It depends upon the structure and location of the summary block. If
only data exists in column C and it is contiguous, then something like

=INDIRECT(ADDRESS(COUNTA(C:C),3))

will work. The formula can be adjusted if the summary block also has
information in column C

=INDIRECT(ADDRESS(COUNTA(C10:C65536),3))

The key is that the VBA populated data must be contiguous for this
approach to be successful...ron