Results 1 to 4 of 4

Thread: Flexible and conditional drop-down list
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Flexible and conditional drop-down list

    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!

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,059
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Flexible and conditional drop-down list

    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/art...a-12651785d29d
    Last edited by Yongle; Jun 7th, 2019 at 11:03 AM.

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Flexible and conditional drop-down list

    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

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,059
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Flexible and conditional drop-down list

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •