1 of 1 people found this helpful
You can't use table calcs from one sheet in another sheet. Table calcs are calculated based on the table of the sheet where they are being used.
So you will need to calc those values all in the same sheet. And that means you can't use standard filters on the sheet where you need both sets of values. (If you filter out year-1, those rows aren't available for calculation3.)
So you need to find a way to specify what the user wants to see using parameters instead. Bring in all the rows into the sheet, use params to control what the table calcs do, calc the calcs you need, and display what you need to display. Table calc filters will leave all the rows intact in the underlying table, but control what part of the table to display on the sheet.