Reference filtered view-specific data

Is there a way to reference the averages shown on the filtered view in another sheet?
(Either the average in the gray sub-header, or in N1. The value in N1 obviously changes when the filtered view is removed.)

Welcome to the community, @Becky_Su .

You can reference cells from different sheets in the same workbook. In this particular case you can reference cell N1 using Excel cell-reference notation as “Sheet1”!N1 ( assuming Sheet1 is the name of the sheet ).

On recomputation as filter rules change, latest values should reflect in cross-sheet formulas.

Hi @msiraj , thank you for the response.
To be clear, this is a filtered view, so N1’s value changes based on the filter—so for example, only T-shirts.
In the non-filtered view, it shows the average cost of any item.
In the “T-shirts only” filtered view, this value changes to show the average cost for a T-shirt—this is what I’d like to show on another sheet, not the cost of any item.

If I reference N1 in another sheet as Sheet1”!N1, it will show the average cost of any item. I was wondering if there is a way to reference the filtered average T-shirt cost.

On switching views, Sheet1!N1 should update to show the current value corresponding to the current view filter even when used in other sheets.

However, a better alternative will be to use, AVERAGEIFS formula in the other sheet and specifying the filter criteria directly in the formula to select only average cost of T-SHIRTS etc.

1 Like

Thank you so much! AVERAGEIFS was perfect!

1 Like