+ Reply to Thread
Results 1 to 6 of 6

Limiting filter criteria depending on cell value

  1. #1
    Registered User
    Join Date
    05-15-2022
    Location
    Gloucestershire UK
    MS-Off Ver
    Windows 11 / Excel 2019
    Posts
    3

    Limiting filter criteria depending on cell value

    My worksheet relates to a gardening ?almanac?. It lists:
    1. Every commodity I might want to sow (flowers, herbs veg, etc)
    2. All tasks related to that commodity and an action date
    3. Usually in chronological order (But as an Excel Table so?)
    The dataset is currently almost 700 lines.

    I would like to set up a filtering option as shown (B2:H3) on the sample dataset, such that when a particular Commodity is selected then only the related Varieties show in the Variety drop-down.
    EG: Commodity ? Lettuce should limit Varieties to Arctic King/Lobjoits Green/Winter Density/Winter Marvel and so on.

    On-Line I found, as an example of data-validation-based-on-another-cell:
    exceldemy.com/excel-data-validation-based-on-another-cell/ #1_Applying_INDIRECT_Function

    This example uses three Tables to select Ice-cream or Juice flavours. Would I have to set up an individual Table for each Commodity and list the relevant varieties or is there a way to glean the varieties from the main un-filtered table? I am NOT expecting anyone to do this for me ? just point me in the right direction please.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,619

    Re: Limiting filter criteria depending on cell value

    Hello Beepee33 and Welcome to Excel Forum.
    It seems to me that an easier way to do this would be to produce a pivot table with the commodities listed in a slicer as modeled.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    05-15-2022
    Location
    Gloucestershire UK
    MS-Off Ver
    Windows 11 / Excel 2019
    Posts
    3

    Re: Limiting filter criteria depending on cell value

    Hi, JeteMc. --
    That looks an interesting possibility, thank you.
    I have only recently started looking at Pivot Tables and don't know Slicers at all.
    Looks like something I need to learn!!
    Thanks again.
    Beepee

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,619

    Re: Limiting filter criteria depending on cell value

    You're Welcome and thank you for the feedback. If the proposed solution answers your question, then please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

  5. #5
    Registered User
    Join Date
    05-15-2022
    Location
    Gloucestershire UK
    MS-Off Ver
    Windows 11 / Excel 2019
    Posts
    3

    Re: Limiting filter criteria depending on cell value

    The pivot table thing may be ok. However, as I understand it (as a novice with pivot tables) the pivot table data is held in memory and is not 'using' the spreadsheet data other than at the start. Will I be able to make changes in the pivot table area and have them reflected in the spreadsheet? While I'm here has anyone a pointer to a good, but free, Pivot Table tutorial
    Many thanks again
    Beepee

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,619

    Re: Limiting filter criteria depending on cell value

    If I understand the question correctly, no, changing the pivot table will not change the spreadsheet (source data).
    Here is a link to Microsoft's tutorial: https://support.microsoft.com/en-us/...9-f99134456576
    There are lots of others. I tend to like the way "Excel is Fun" and "Excel Campus" present information.
    I hope that this is helpful.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 06-28-2020, 11:54 AM
  2. [SOLVED] Add additional filter criteria depending on cell value
    By dancing-shadow in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-14-2017, 10:49 AM
  3. Set a filter in a table depending on content of cell
    By fxmu in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-14-2015, 09:55 PM
  4. Advanced Filter : One Criteria Cell Breaks the Filter - Returns Only Headers
    By PaulGW in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2014, 10:06 AM
  5. Limiting a certain number of rows with advanced filter
    By roseieroses in forum Excel General
    Replies: 5
    Last Post: 04-24-2013, 03:43 PM
  6. Auto Filter depending on cell values
    By foxglove666 in forum Excel General
    Replies: 1
    Last Post: 05-01-2008, 05:01 PM
  7. [SOLVED] limiting chart source data with criteria?
    By [email protected] in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-19-2006, 04:55 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1