Evaluate a column and extract last value
 

I know there's an easy way to do this, but I've had a brain freeze for too
long. I have several worksheets that keep a running balance of various
accounts. I want to take the outstanding balance on each worksheet to get a
total of all sheets. I've found a method that works, but it's convoluted and
looking to stop these headaches!
Col F
-----
Running balance that is empty unless there's an entry in Cols D or E

Col G
-----
Simple "=F" but gives text "blank" if no entries.

Col H
-----
=IF(AND(G9="blank"),IF(G8<$G$1,G8,0)) with this formula at H9, where G1=0,
I get an "ERROR" in rows that have data, the text "blank" in unused rows and
a figure in the last used row.

I then sum Col H to the same cell in all worksheets and get my grand total
from these. PHEW.....OK, so what's the ez solution?? This works, but I HATE
not seeing the obvious ez solution. Thanks for your thoughts!

--
Virg

Evaluate a column and extract last value
 

I'd probably put the running balance in Column F and then conditional format
it to not display if there was nothing in columns D or E. Use something
like this for your conditional format formula.

=AND(ISBLANK($D1),ISBLANK($E1))

I'd probably conditional format column G the same way.

I've gotta go and can't figure out the rest right now. Good luck!

"Virg" wrote:

I know there's an easy way to do this, but I've had a brain freeze for too
long. I have several worksheets that keep a running balance of various
accounts. I want to take the outstanding balance on each worksheet to get a
total of all sheets. I've found a method that works, but it's convoluted and
looking to stop these headaches!
Col F
-----
Running balance that is empty unless there's an entry in Cols D or E

Col G
-----
Simple "=F" but gives text "blank" if no entries.

Col H
-----
=IF(AND(G9="blank"),IF(G8<$G$1,G8,0)) with this formula at H9, where G1=0,
I get an "ERROR" in rows that have data, the text "blank" in unused rows and
a figure in the last used row.

I then sum Col H to the same cell in all worksheets and get my grand total
from these. PHEW.....OK, so what's the ez solution?? This works, but I HATE
not seeing the obvious ez solution. Thanks for your thoughts!

--
Virg

Evaluate a column and extract last value
 

Why not just use

=SUM(D:D,E:E)

?

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Virg" wrote in message
...
I know there's an easy way to do this, but I've had a brain freeze for too
long. I have several worksheets that keep a running balance of various
accounts. I want to take the outstanding balance on each worksheet to get
a
total of all sheets. I've found a method that works, but it's convoluted
and
looking to stop these headaches!
Col F
-----
Running balance that is empty unless there's an entry in Cols D or E

Col G
-----
Simple "=F" but gives text "blank" if no entries.

Col H
-----
=IF(AND(G9="blank"),IF(G8<$G$1,G8,0)) with this formula at H9, where
G1=0,
I get an "ERROR" in rows that have data, the text "blank" in unused rows
and
a figure in the last used row.

I then sum Col H to the same cell in all worksheets and get my grand total
from these. PHEW.....OK, so what's the ez solution?? This works, but I
HATE
not seeing the obvious ez solution. Thanks for your thoughts!

--
Virg

Evaluate a column and extract last value
 

Hi,

This should return the last text value in Col H (change range as
required)

=LOOKUP(9.99999999999999E+307,H1:H2000)

or this array (ctrl + shift + enter) will return the last entry

=INDEX(H1:H2000,MAX(IF(H1:H2000<"",ROW(H1:H2000)) ))

VBA Noob


Virg wrote:
I know there's an easy way to do this, but I've had a brain freeze for too
long. I have several worksheets that keep a running balance of various
accounts. I want to take the outstanding balance on each worksheet to get a
total of all sheets. I've found a method that works, but it's convoluted and
looking to stop these headaches!
Col F
-----
Running balance that is empty unless there's an entry in Cols D or E

Col G
-----
Simple "=F" but gives text "blank" if no entries.

Col H
-----
=IF(AND(G9="blank"),IF(G8<$G$1,G8,0)) with this formula at H9, where G1=0,
I get an "ERROR" in rows that have data, the text "blank" in unused rows and
a figure in the last used row.

I then sum Col H to the same cell in all worksheets and get my grand total
from these. PHEW.....OK, so what's the ez solution?? This works, but I HATE
not seeing the obvious ez solution. Thanks for your thoughts!

--
Virg