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.
-- Johnny
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
