Find first previous cell with data

[Nancy Newburger]

Hi, I am not "fluent" in Excel, so please make any answers "dummy" level.

I have a running balance column that spans multiple pages and I am trying to
get the last balance from the previous page onto a cell in the following page.

Additionally, there are column headings at the beginning of each page and I
have all cells (except column heading cells) in this balance column
conditionally formatted to not display duplicate balances, so there are cells
that appear blank but technically are not.

I read a post in General Questions dated 1/16/2006 by Derby Jim and answered
by Bob Phillips that sounded like my situation. I tried both solutions that
Bob presented and neither worked. I set up test worksheet as exampled in the
post, used both formulas and both returned 0.

Any help would be much appreciated.

[Mark]

Here is a formula that may be more or less what you need

=INDIRECT("A"&COUNTA(A1:A100))

This formula will cause the cell that contains it to display the
contents of the last cell in column A that isnt blank. For the
example, I only had it count from A1 to A100, but you may change that
to whatever number you wish. This will only work for you if there are
NO BLANK CELLS within the range being counted before the last non blank
cell.

Your actual formula may look more like this:
=INDIRECT("A"&COUNTA(Sheet3!A1:A10))
due to the fact that you are traversing different worksheets. If you
have any questions, gimme a shout.

[Nancy Newburger]

Mark,
Thank you for responding to my post. I tried the formula you suggested, it
did not work. I got a NAME error.

As my running balance column is I, I typed in the formula as follows:
=INDIRECT("I"&COUNTI(A4:A10000)). I started with A4 as that is where my data
starts-2 rows of column headers and a blank row before the first line of
data. I typed in the formula in row 3 of page 3-2 rows of column headers
precede the formula. As far I know I do not have any blanks.

Hopefully you can shed some light.

Thanks, Nancy

"Mark" wrote:

>
> Here is a formula that may be more or less what you need
>
> =INDIRECT("A"&COUNTA(A1:A100))
>
> This formula will cause the cell that contains it to display the
> contents of the last cell in column A that isnt blank. For the
> example, I only had it count from A1 to A100, but you may change that
> to whatever number you wish. This will only work for you if there are
> NO BLANK CELLS within the range being counted before the last non blank
> cell.
>
> Your actual formula may look more like this:
> =INDIRECT("A"&COUNTA(Sheet3!A1:A10))
> due to the fact that you are traversing different worksheets. If you
> have any questions, gimme a shout.
>
>

[Mark]

Your are close but just off

=INDIRECT("I"&COUNTI(A4:A10000)) needs to be:
=INDIRECT("I"&COUNTA(A4:A10000)).

I can see why you did that but you are using the COUNTA function.
Should work now...