Ask Your Question
1

How can you create a summary sheet of another sheet in Calc

asked 2016-10-11 20:44:31 +0200

migueldealmeida gravatar image

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 flag offensive close merge delete

Comments

Upvoted your question as you provided example, you should now be able to attach files to your questions. If you could please attach files in future instead of adding links to external sites

mark_t gravatar imagemark_t ( 2016-10-11 21:24:10 +0200 )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)

migueldealmeida gravatar imagemigueldealmeida ( 2016-10-12 10:13:13 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2016-10-11 21:18:59 +0200

mark_t gravatar image

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.

edit flag offensive delete link more

Comments

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.

migueldealmeida gravatar imagemigueldealmeida ( 2016-10-12 10:11:19 +0200 )edit
1

answered 2016-10-12 02:45:44 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more

Comments

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"?

migueldealmeida gravatar imagemigueldealmeida ( 2016-10-12 10:01:51 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2016-10-12 12:20:42 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-10-11 20:44:31 +0200

Seen: 142 times

Last updated: Oct 12 '16