I need to do a report on rental property. I've figured out that the best
solution seems to be a consolidation project.

Sheet 1 will be the report proper.

There will be a fixed format for each sheet. At the top of each sheet there
will be an area that has the fixed details of the lease.....leasee, county,
legal description, etc.

Then will start a series of rows numbering from 1 to n. Each row will have
the details (Start, end, payment interval, lease amount, etc.) of a lease
with the nth row containing the current lease.

I need to post forward some of the fixed details. That's easy. But I also
need to find the current lease which will be the last row completed. How do I
find that last row by formula? I need to use a formula for each lease on the
first page sothat the report is always current.

Clear enough to provide an answer?

Thanks.

Eric Johnson

Hi,

If you don't know which column this is in then it will be difficult with a
formula but if you know the column then this does it for column A

=INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A50000))*(A1: A50000<""))))

change 50000 to a realistic likely number greater than your last row

Mike

"Eric" wrote:

I need to do a report on rental property. I've figured out that the best
solution seems to be a consolidation project.

Sheet 1 will be the report proper.

There will be a fixed format for each sheet. At the top of each sheet there
will be an area that has the fixed details of the lease.....leasee, county,
legal description, etc.

Then will start a series of rows numbering from 1 to n. Each row will have
the details (Start, end, payment interval, lease amount, etc.) of a lease
with the nth row containing the current lease.

I need to post forward some of the fixed details. That's easy. But I also
need to find the current lease which will be the last row completed. How do I
find that last row by formula? I need to use a formula for each lease on the
first page sothat the report is always current.

Clear enough to provide an answer?

Thanks.

Eric Johnson

"Mike H" wrote:

If you don't know which column this is in then it will be difficult with a
formula but if you know the column then this does it for column A

=INDIRECT("A"&SUMPRODUCT(MAX((ROW(A1:A50000))*(A1: A50000<""))))

change 50000 to a realistic likely number greater than your last row

I'll have to experiment. I may be able to do a series of columns (all will
be the same format from shhet to sheet and within a sheet) but at worst I can
plug column formulas in one by one into a single row for the sheet and then
copy that row forward to Sheet 1. Probably better that way anyway.

Thanks for your help.

Eric