Forum Discussion
Help with linking cell to a filtered spreadsheet
You can use Excel Tables feature and for filters use the Table Slicers and then your chart data will be dynamic based on Excel Table Dynamic Range Feature (meaning adding or removing data automatically reflects in your chart and you do not have to modify your charts source range) and also you can use the Table slicers to put filters on and off.
Thank you so much, Jamil! One follow-up question: I am trying to put formulas in my statistics chart that automatically reflect the total events hosted by each practice group (see the screenshot below). I tried doing this using the SUBTOTAL Function with my TableSlicers, but that makes my "Totals" change in the chart when I change the Table Slicer. For example, in the screenshots below when I filter for Alternative Investments, it says 7 total events, which I can then link to my statistics chart in Sheet 2. However, when I change the filter to Financial Restructuring, the total events in 9, which ends up changing ALL of the totals in my chart to 9. is there anyway to lock the formulas in the chart so that they only reflect the Subtotal of when Practice Area filter is a certain value AND Event type (Firm-Hosted v. Sponsored) is set to a certain value?
Thank you!
Taylor
Thanks for the feedback.
you should not use the actual cell references in your subtotal formula.
you should use Excel Tables Structural References, so that it becomes dynamic.
plz see the example i uploaded here.