+ Reply to Thread
Results 1 to 3 of 3

Making Multiple Dependable lists based on data in table

  1. #1
    Registered User
    Join Date
    07-10-2018
    Location
    australia
    MS-Off Ver
    2016
    Posts
    23

    Making Multiple Dependable lists based on data in table

    Hi Everyone,

    This is probably an easy solve but i cant work it out so any help would be appreciated, I have attached the file to see exactly what I'm after -

    Sheet 1 = QuoteForm
    Sheet 2 = Table
    Sheet 3 = Pivot Table

    I have set up 4 columns on quoteform to be List's - List 1, List 2, List 3 and List 4. when I select an item in select List 1 I want List 2 to update and be dependable on 1 and then List 3 on 2 and List 4 on 3.

    there will be many duplicates which will not be the whole row, my table will give you an idea of what i mean.

    My dependable lists are currently referenced to the pivot tables due to the duplicates in the table.

    Im currently stuck on how to make the lists dependable without creating named ranges for each separate item in my table, as this will be to time consuming and painful to add new items in.

    I don't know my way around pivot tables that well and I cant seem to workout a formula for named ranges that will do the job i want.

    thanks in advanced.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Making Multiple Dependable lists based on data in table

    Redesign pivot per attached,

    Range name
    List1
    =OFFSET('Pivot Tables'!$B$2,,,COUNTA('Pivot Tables'!$B:$B)-1)

    List2
    =OFFSET('Pivot Tables'!$E$1,MATCH(D18,'Pivot Tables'!$D$2:$D$999,),,COUNTIFS('Pivot Tables'!$D$2:$D$999,D18))

    List3
    =OFFSET('Pivot Tables'!$I$1,MATCH(D18&E18,INDEX('Pivot Tables'!$G$2:$G$99&'Pivot Tables'!$H$2:$H$99,),),,COUNTIFS('Pivot Tables'!$G$2:$G$99,D18,'Pivot Tables'!$H$2:$H$99,E18))

    List4
    =OFFSET('Pivot Tables'!$L$1,MATCH(F18,'Pivot Tables'!$K$2:$K$99,),,COUNTIFS('Pivot Tables'!$K$2:$K$99,F18))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-10-2018
    Location
    australia
    MS-Off Ver
    2016
    Posts
    23

    Re: Making Multiple Dependable lists based on data in table

    that's awesome thanks a lot, you dont want to know how long i've tried creating this.

    when you double click on list 1 it expands automatically with all the items with code running behind the double click, is there a way to have the same for list 2 after a selection is made in list 1?

    currently it opens an empty box.

+ 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. Dependent lists based on multiple not unique table columns
    By jaryszek in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-18-2020, 09:59 AM
  2. Making calculations based on data appearing on Pivot Table (not its source data)
    By MindYourLanguage in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-24-2020, 11:45 AM
  3. Making 2 Dependable drop downs
    By sravanasandhya in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-16-2019, 02:44 AM
  4. Need help making multiple lists that are dependent on the first list.
    By bender1227 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-24-2019, 02:36 PM
  5. Dependable drop down lists- multiple rows.
    By crowmatt in forum Excel General
    Replies: 2
    Last Post: 03-31-2017, 09:10 AM
  6. Replies: 0
    Last Post: 07-01-2015, 09:49 AM
  7. Dependable Data Validation; Multiple data sets
    By Expod in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-06-2012, 06:04 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