Sorting a List by using dropdowns

[lost_in_the_sauce]

My data set is categorized by store/metric/month, with store in column A, metric (sales, cogs, payroll, etc) in column B. C1-N1 is Jan-Dec, and everything below that is numbers pulled from a tab that contains a data export from Smartsheets. Column A & B repeat enough that there is every possible store/metric combo.

I'd like to have another tab that has a lost of every store in columns with all the metrics in the rows that will populate/reorder high to low based on 2 drop down boxes - one for month and one for metric.
Is this possible? I've been stumped trying to engineer it.

 

[SanjayGMusafir]

I haven't, I don't have tons of experience with slicers

Just convert your data to tables. On tables tab you will find Slicers.

Try that, probably that will solve your requirement in just 2 clicks.

 

[lost_in_the_sauce]

Yeah, but the problem is when I use a slicer on the table when try to sort a column, the formulas screw up

 

[SanjayGMusafir]

when I use a slicer on the table when try to sort a column, the formulas screw up

Yes that could be a problem. I just checked -

What you have done that you have pulled main data using SUMPRODUCT with correct referencing.

But with % you have simply put formulas - Ideally you should do % calculations separately.

In fact now I realise, the data you have shared can be derived using Pivot tables without getting into hazard of populating formulae.

With Excel 365 in your hand, I would suggest try using Analytical tools like Pivot Tables, Slicers just be converting your main data to tables.

With the tools I have mentioned, you can play with your basic data without even disturbing it.

Explore them and you shall realize the power of Excel. Everyone starts as a novice.