Right you clever people..who uses Openoffice spreadsheets?

alxx_b

I'm trying to set myself up a spreadsheet from imported statements from my bank accounts.

I've managed to put together a basic SS and copied the last year's worth of statements into it.

Now I'm not sure how much OpenOffice can do, but would like if it would automatically filter the amounts (or copy them over) from each row as per the transaction description (Aldi, WHSmith, Cash etc) into the correct columns so I can see what has been spent on what.

Is this possible? Or do I have to copy every single transaction by hand?

TheTracker

In Excel you'd use a Pivot Table. In Open Office it is apparently called DataPilot. If you search for datapilot tutorial it should come up trumps.

polymaff

alxx_b wrote: »

I'm trying to set myself up a spreadsheet from imported statements from my bank accounts.

I've managed to put together a basic SS and copied the last year's worth of statements into it.

Now I'm not sure how much OpenOffice can do, but would like if it would automatically filter the amounts (or copy them over) from each row as per the transaction description (Aldi, WHSmith, Cash etc) into the correct columns so I can see what has been spent on what.

Is this possible? Or do I have to copy every single transaction by hand?

Don't use the statements. Look for, and use, CSV/Excel downloads. They're easier to find with some banks than others - but they're there for Lloyds, TSB, Halifax, Natwest, Santander, Nationwide etc. They'll open directly in OpenOffice and will cut out manual transcription entirely.

maas

TheTracker wrote: »

In Excel you'd use a Pivot Table. In Open Office it is apparently called DataPilot. If you search for datapilot tutorial it should come up trumps.

I've got Open Office v4 and its just named Pivot Tale (under Data in the menu bar).

OP just pick Pivot Table, and the select the table (it should auto select for you).

Then using the wizard drag the statement columns into place;
Item to Row
Amount to Data

grumbler

alxx_b wrote: »

...
Now I'm not sure how much OpenOffice can do, but would like if it would automatically filter the amounts (or copy them over) from each row as per the transaction description (Aldi, WHSmith, Cash etc) into the correct columns so I can see what has been spent on what.

Is this possible? Or do I have to copy every single transaction by hand?

Excel has a very advanced system of filtering, and has several useful functions like SUBTOTAL and SUMIF. I guess OO has something similar.

anamenottaken

grumbler wrote: »

Excel has a very advanced system of filtering, and has several useful functions like SUBTOTAL and SUMIF. I guess OO has something similar.

True, but that's not the application the OP is using, is it?

grumbler

That's why I said: "I guess". AFAIK OO is similar to Excel in many aspects, and if it does have filter it can be simpler to use than a Pivot Table.

Archi_Bald

There's nothing like an exchange of views between technical giants :cool:

Go on, let's have some more!

polymaff

Archi_Bald wrote: »

There's nothing like an exchange of views between technical giants :cool:

Go on, let's have some more!

I'm no Excel Guru, but try:

=ISNUMBER(SEARCH("waitrose";E464))*I464

Where E464 is the transaction descriptor cell and I464 is the transaction amount cell. It returns either zero or the transaction amount depending upon the presence of case-insensitive "string"

Checked in Open Office 4.1.0. Seems to work.

(Puts tin hat on )

polymaff

Or, for a single summary of a number of spreadsheet rows:

=SUMPRODUCT((ISNUMBER(SEARCH("sainsbury",E464:E503)))*(I464:I503))

will return the total of "sainsbury" purchases in the range of rows specified.