Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to Extract value on 3 column based on two conditions | Excel Discussion (Misc queries) | |||
extract specific info from cells in a column | New Users to Excel | |||
Extract Unique entries in a column | Excel Worksheet Functions | |||
extract column or row reference | Excel Discussion (Misc queries) | |||
Extract AutoFilter Column Values? | Excel Discussion (Misc queries) |