On Wed, 09 Jan 2008 16:28:19 +0900
James Elliott - WA Rural Computers wrote:
> Hi - it's James again with his Calc cashbook and another challenge ...
>
> I have used array functions to add up all the debits and credits for
> differing categories, eg:
> my code, in Column A, for vehicle expenses is "v" and if I want to
> know how much I spent on vehicles in the financial year ending
> 30/6/07, this formula will answer my query:
>
> {=SUM((E7:E2000)*(A7:NAB.A2000="v")*(B7:B2000>=D4)*(B7:B2000<=D5))}
>
> where D4 is the start date, 1/JULY/06 in this case, and
> where D5 is the end date, or 30/JUNE/07, and
> where E is the debits column (expenditure), and
> where B is the Date column
>
> In pseudocode: if(code=i) and if(D4<=Date<=D5) then SUM(Column E)
>
> The actual formula I use is complicated by the addition of
> Sheet-names and generous usage of the $ symbol to make all references
> Absolute (as distinct from Relative), so the formula does not change
> when copied from cell to cell, and I think I might have made a typos
> because it did not work properly when tested.
>
> In the end, I selected all the cells and hit Delete, and they all went
> blank, as one would expect.
> Now, when I try to enter anything into the cell I get a pop-up error
> message saying:
> "You cannot change only part of an array"
>
> If I select a cell the array formula is still there, but if I try to
> delete it, I get:
> "You cannot change only part of an array"
> and if I try and edit it (or delete it) in the formula bar, I get:
> "You cannot change only part of an array"
>
> So, I am sorta stuck ... I cannot delete my formulas and cannot
> proceed until I do.
>
> Any advice or suggestion would-be most welcome.
>
> James
>
>
Cheat:
* Create a new sheet
* Copy the relevant bits
* Delete the old sheet
(no array exists on the new sheet)
--
Michael
All shall be well, and all shall be well, and all manner of things shall
be well
- Julian of Norwich 1342 - 1416
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
