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.)
Thanks.
Welcome to the Spreadsheet.com 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