How can you create a summary sheet of another sheet in Calc [closed]

Consider this Calc file:

1. Sheet Complete has a table with 8 columns
2. Sheet Summary should be a summary of that table:

-I only want to see 4 of the columns

-I only want to see the rows whose Type is "Cash" in the Complete sheet

Is it possible to auto-build the Summary Sheet from the Complete sheet? How would it be done?

edit retag reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-09-13 17:22:00.072931

( 2016-10-11 21:24:10 +0100 )edit

Thanks @mark_t! However I believe I'm unable to edit the question to upload the example directly. (and you're right, I needed to use an external service since I didn't have permissions to upload a file)

( 2016-10-12 10:13:13 +0100 )edit

Sort by » oldest newest most voted

Updated your example attached Summary table example.ods

Column H is helper column to select unique list from type column.

Data validity chooses the value from the list of unique types in the yellow cell. Validity list is set to sort ascending order.

Column F is helper column to select rows from class column to match the type selected by the yellow cell.

Formula in A2 to D22 uses the row number in helper column F to show the filtered data.

Copy the formula further down the sheet if needed.

It runs a bit slow because I use full length of columns in some formula, example $H:$H, if you know the possible size of your data you could use for example $H$1:$H$100 for 100 rows.

more

Thank you for the example! I will test this in terms of speed and compare it with a Pivot Table approach, but I'll accept this answer given it solves the problem with a working example.

( 2016-10-12 10:11:19 +0100 )edit

I think just for that are Pivot Tables.

Go to one cell on the table's header and then Menu/Insert/Pivot table.

Drag the fields as Row / Column / Data fields.

If you want to summarize dates by months etc, go to a column with them on the pivot table and use [F12] Menu/Data /Group&Outline/Group.

more

I've managed to get a table with Date, description, total and type columns by putting those in the "Row fields". However, how is it possible in the PIvot Table Layout to say "only those whose type is Cash"?

( 2016-10-12 10:01:51 +0100 )edit

Select it clicking the filter arrow on the head of the field or adding the field to Page fields on the design view.

( 2016-10-12 12:20:42 +0100 )edit