Finding Last Relevant Cell

[Mae]

This is probably more info than you need, and I'm sure
it's either a simple answer or impossible, but here
goes....

I have a workbook with worksheets "Log"
and "Currency". "Log" tracks withdrawals and deposits to
a petty cash account, and "Currency" shows actual cash on
hand.

In "Log" I have columns Start Balance, Withdrawal,
Deposit, and End Balance. Start Balance is a formula so
that Start Balance of Row2 = End Balance of Row1. End
Balance is a formula so that End Balance = Start Balance -
Withdrawal + Deposit. I didn't want to copy the Start and
End Balance formulas individually each time I made an
entry, so I copied them down each column for several
pages. But then I didn't want to have the values show up
for several pages of "blank" rows that didn't have a
transaction. To fix this, I conditionally formatted both
columns so that if Start Balance = End Balance -- meaning
that row does not have a transaction -- the font for those
columns is white. Now, although the values are always
there, they are only visible if a withdrawal or deposit is
recorded in that row.

I want to have a cell in "Currency" that will calculate
the difference between my cash on hand, and the last valid
End Balance. Currently, I have to update the formula
every time I make an entry in the Log. i.e. manually
changing "=Log!L172-Cash" to "=Log!L173-Cash". Isn't
there some formula that can find the last valid entry,
maybe an If formula using the same criteria that I used in
my conditional formatting? I thought about finding the
last black cell and then going up one, but the cells are
not actually blank.

Any help is greatly appreciated

 

[Kevin M]

Mae,
This formula will find and return the last figure entered
in the given range of cells. You can then base your
subtraction formula off of this so now it can be "This
cell" - cash.
HTH
Kevin M

=OFFSET(A1,COUNTA(A$1:A$100)-1,0)

 

[Anders S]

Mae, try this,

Assuming your table is in columns A to D, with the headers in row 1 (you may have to adjust references to suit your actual sheet).

In sheet "Log", column D - End Balance - enter
=IF(SUM(B2:C2)=0,"",A2-B2+C2)
this will put an empty value in the cell if cols B and C are empty. You no longer need Conditional Formatting for col D.

In sheet "Currency" enter
=OFFSET(Log!D1,MATCH("",Log!D,0)-2,0)
the formula will find the first empty cell in col D and return the value above it.

HTH
Anders Silvén

 

[Mae]

YOU ROCK!!

That's exactly what I was trying to do! I've never used
Offset or Match before, I think they'll come in handy.

Thanks a million.

-----Original Message-----
Mae, try this,

Assuming your table is in columns A to D, with the

headers in row 1 (you may have to adjust references to
suit your actual sheet).

In sheet "Log", column D - End Balance - enter
=IF(SUM(B2:C2)=0,"",A2-B2+C2)
this will put an empty value in the cell if cols B and C

are empty. You no longer need Conditional Formatting for
col D.

In sheet "Currency" enter
=OFFSET(Log!D1,MATCH("",Log!D,0)-2,0)
the formula will find the first empty cell in col D and return the value above it.

HTH
Anders Silvén

"Mae" <[email protected]> skrev i

meddelandet news:[email protected]...