How do I filter a table where I use offset?

calyx_teren
calyx_teren used Ask the Experts™
on
Hello, I have a table that uses offset to show the top 10 downloads of all time, but I need this table to be dynamic and filterable. The table must be able to filter by date so that it shows the top 10 downloads for a given month or quarter that the user selects.
top-10-downloads-table.PNG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
I would suggest converting it to a PivotTable. That way you can easily filter by date, and it's probably easier to maintain.

If you don't want a PivotTable for some reason (and I would strongly suggest you do), then there are ranking functions such as INDEX and MATCH you can do, but it's much more complicated.

Either way, a post of your spreadsheet would help.

Author

Commented:
Hi Phillip, thanks for your suggestion. I'm going to try this and see if I can get it to rank the files. Since this table is on a dashboard, I'd like to add a combo box (i.e. dropdown list) that the user can use to filter the table by month or quarter. As you can see, I did this for a pivot chart that I use on the dashboard. Ideally, I'd like for the dropdown list I currently have to trigger another dropdown list to appear which will list either months or quarters. Any suuggestions you have on how to improve the dashboard are appreciated.
Dashboard-Mockup-v6-dummy-data.xlsm
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
I note your comment in Website1!E5, namely "This column does not scroll. I haven't been able to figure out how to fix it.". If you change the formula from:

=OFFSET(calculations1!O3,calculations1!$K$2,0)

to

=OFFSET(calculations1!O3,calculations1!$K$2+calculations1!$D$3,0)

it will then scroll.

If you are using Excel 2010, 2013 or Office 365, I would also suggest using slicers as well as the PivotChart you have in Website1, so that the end user can narrow down the time period for instance.
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
Example of a slicer in action attached.

To do that, click on the PivotChart and go to PivotChart - Analyse - Insert Slicer.

or, if you have the latest version, - Insert Timeline.Slicer
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Then, you can change the table in cells C4:F14 to a PivotTable, and have them both be controlled by the slicer/timeline.

Author

Commented:
Thank you, Philip. I went with a pivot chart and a pivot table that is controlled with a slicer. Your answer for how to make the first table I had scroll worked, as well. The pivot table for the articles will be much easier to maintain than the Offset table I was using. Thanks again.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial