Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Last Figure
Hello Programmers, Example: I have a list of figures ranging from A1:A12. These cell contain formulas. If I have currency values in A1:A5, A:6 down to A:12 will have $0.00 because those cells haven't been updated yet. How do I display the last figure in the list in cell B1 (which in thi case would be in A5); Without the $0's showing up as a value? Then a A6 updates, and it's the last value in the list, then that's what shown in B1. Thanks, EMo -- EMo ----------------------------------------------------------------------- EMoe's Profile: http://www.excelforum.com/member.php...fo&userid=2318 View this thread: http://www.excelforum.com/showthread.php?threadid=47458 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Last Figure
Try this formula
=INDEX(A1:A12,MAX(IF(A1:A12<0,ROW(A1:A12)))) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH Bob Phillips "EMoe" wrote in message ... Hello Programmers, Example: I have a list of figures ranging from A1:A12. These cells contain formulas. If I have currency values in A1:A5, A:6 down to A:12 will have $0.00, because those cells haven't been updated yet. How do I display the last figure in the list in cell B1 (which in this case would be in A5); Without the $0's showing up as a value? Then as A6 updates, and it's the last value in the list, then that's whats shown in B1. Thanks, EMoe -- EMoe ------------------------------------------------------------------------ EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183 View this thread: http://www.excelforum.com/showthread...hreadid=474585 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Last Figure
On Sun, 9 Oct 2005 08:00:33 -0500, EMoe
wrote: Hello Programmers, Example: I have a list of figures ranging from A1:A12. These cells contain formulas. If I have currency values in A1:A5, A:6 down to A:12 will have $0.00, because those cells haven't been updated yet. How do I display the last figure in the list in cell B1 (which in this case would be in A5); Without the $0's showing up as a value? Then as A6 updates, and it's the last value in the list, then that's whats shown in B1. Thanks, EMoe Formula Solution: You could use this **array** formula in B1: =OFFSET(A1,-1+MAX((A1:A120)*ROW(A1:A12)),0) To enter an array formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. ---------------------- VBA Solution Right click on the sheet tab and select View Code. Paste the code below into the window that opens. ======================== Private Sub Worksheet_Change(ByVal Target As Range) Dim AOI As Range Dim c As Range Dim Res As Range Application.EnableEvents = False Set AOI = [A1:A12] Set Res = [B1] For Each c In AOI If c.Value = 0 Then Res.Value = c.Offset(-1, 0).Value GoTo X End If Next c X: Application.EnableEvents = True End Sub ====================== Neither routine checks for invalid entries but rather assumes that the only entries will be 0's (to be ignored) or other values to be considered. --ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Last Figure
Thanks Guys, I'll try both solutions. Regards, EMoe -- EMoe ------------------------------------------------------------------------ EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183 View this thread: http://www.excelforum.com/showthread...hreadid=474585 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW TO 'BLINK' A FIGURE INSTEAD OFF BOLDING THE FIGURE | Excel Worksheet Functions | |||
Change a specific figure i.e 0:00 and 24:00 to display as "MIDT" | Excel Discussion (Misc queries) | |||
Can't figure it out . . . | Excel Worksheet Functions | |||
To Display a figure while excel is ready to GO! | Excel Programming | |||
Can't figure it out | Excel Programming |