Cross referencing data between sheets when rows are inserted

[TheOther]

I have a Libreoffice Calc spreadsheet where I have entered lots of data into the main sheet.

Sometime in the past I created a summary sheet with data copied from four columns and I sorted it in order from two of those columns. I created this by referencing the top row from the main sheet and copying the references down to the end of my data. I then sorted it on the desired columns.

Since then I have inserted more rows into the main sheet. Naturally these rows that I have inserted are not replicated in the summary sheet. I could re-create the summary sheet by changing row references and copying them down the sheet again. But, is there a more intelligent way to create my summary sheet that will automatically pickup inserted rows?

[schiavinatto]

See Pivot Table.

For a more complete suggestion: post an example file.

[TheOther]

OK, a Pivot Table looks interesting but didn’t (or at least I don’t think it does - it’s the first time I’ve found these) achieve what I want. I did find a set of rows/columns and data field that gave me a table of yield by year & variety but as far as I can see it is not going to give me the desired output (but I might be wrong as I’m not familiar with them).
I’ve created the below example of what I am trying to achieve:
Trial.ods (11.9 KB)
Data sheet is my information. Within this I will insert new rows anywhere within the data. My actual data sheet has many more columns and rows.
Comparison is the set of summary information that I want to generate and be able to sort it by both Type and Yield.

[bantoniof]

The reference form is correct: $Data.I5
But in the Trial.ods spreadsheet there are two problems,

• you are not referencing I5, but I2 (may be a problem when ordering the data)
• The cell format in the Comparison sheet is with one decimal while in Data it is with two decimals