Flexible and conditional drop-down list


New Member
Jun 7, 2019
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!

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.


Well-known Member
Mar 11, 2015
Office Version
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
Last edited:


New Member
Jun 7, 2019
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

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics