Flexible and conditional drop-down list

[rookie_excel]

Hi everyone,

I am working for a project for my company and would need some help on an issue I have.

Ultimately, I want to create a model that is as flexible as a PivotTable without using a PivotTable. The reason for this that I won't be able to change any fields.

I am working with conditional drop-down lists in order to retrieve data that is linked to a SUMIF formula. Even though it is a good start it does not offer me the full flexibility of a PivotTable since the lists are conditional on each other. Let's say that if I have 10 different drop-down lists in Column A to J and the different drop-down lists are dependent on each other. Is there a way I can structure it so that at any point I can retrieve the data based on just column A and D and at the same time just a single column?

If for example, Column A = Country), B = Month), C = Product), D = Area

Column A includes: Germany, Spain and Australia
Column C includes: Coca Cola, Fanta and Water

I want to have the possibility to see the sales for Coca Cola in Germany, but at the same time, I want to have the possibility to see the total sales for Coca Cola based on all the different countries in the drop-down list. Is there a way in which this can be achieved?

I know it works in a PivotTable but I want to know if it's possible achieve the same flexibility by building a model and not using IF function because the database is to large for building such a formula.

Thankful for any suggestions!

 

[Yongle]

Consider converting the range to an Excel table which allows a total row to be added
- select table \ click Table Tools \ click on Total Row

With later versions of Excel slicers can be added to tables to make filtering easy, otherwise filter in usual way (the total changes to match filtered values)
- select table \ click Table Tools \ Insert Slicer

Have a look at the video on this link https://support.office.com/en-us/ar...ter-data-249f966b-a9d5-4b0f-b31a-12651785d29d

 

[rookie_excel]

Hi Yongle,

Thanks for your reply, that's exactly what I did. Do you happen to know if it is possible to link formulas to the slicers?

Thank you

 

[Yongle]

You may need to use AdvancedFilter to give you the total flexibility you require (and then automate with VBA to make life easy for the user)

Have a look at ...

https://trumpexcel.com/excel-advanced-filter/

https://www.contextures.com/xladvfilter01.html