On Sat, 21 May 2005 00:10:12 +0200, Johnny Andersson wrote: >GregChi <[EMAIL PROTECTED]> skrev den Fri, 20 May 2005 15:46:01 -0400: > >> In a check register I want a formula in cell A1 to show the last >> value in the column(D?) that is the current balance. As more data is >> entered this last item will be shifted down. Until data is entered the >> cell is empty. >> Is there a function/formula for this? > >I have two ideas: > >1. This will only work if there are no empty rows between those rows with >data. >Enter the following to A1: >=INDIRECT(ADDRESS(COUNTA(D1:D32000);4)) > >If, for example, the first non empty cell in column D is D2, just modify >the formula as follows: >=INDIRECT(ADDRESS(COUNTA(D1:D32000)+1;4)) >COUNTA(D1:D32000)+1 represent the last used row in this case. >4 represents column D, of course. > >2. This will work in all cases I can think of right now, but I guess it >will work slower and the file size will be larger: >Insert a new E column (which you can hide later). Enter the following into >E2, if D2 is the first cell containing relevant data: >=IF(D3="";D2;0) >Copy this as far down as you think is necessary. For example, E1000 should >contain =IF(D1001="";D1000;0) and so on... >Enter the following in A1: >=SUM(E1:E32000) >If your D column ONLY contains POSITIVE values, =MAX(E1:E32000) also will >work, but why try that when SUM works in all cases? >Now, hide your E column and it will look nicer. > >If method 1 works for you, I guess that's the one to use. Use method two >ONLY if method 1 for some reason can't be used, like if there are empty >cells here and there in column D. > >I can also think of a third solution to this problem: Writing a new cell >function in StarBasic and use it in A1.
Thanks, I mis-stated.. I have a formula in All cells in column D. The ones that have a result are visable. Your method(1) gets the last formula entered but not the last result (sorry abt that...) -- Greg Agent 1.93/32.576 Opera 7.50 3778 OOo 1.1.2 news.individual.net & news.gmane.org Win98se for sale: Apple//,Auto Parts/Equip/Manuals: http://[EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
