+ Reply to Thread
Results 1 to 10 of 10

Dependent Dropdowns & Auto Filling Cells based on table

  1. #1
    Registered User
    Join Date
    07-26-2020
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    6

    Dependent Dropdowns & Auto Filling Cells based on table

    Hi there,

    First time here and first post - thanks for reading and helping!

    I'm trying to create a spreadsheet to help with a friend's Event Planning business.
    The idea is that there is a table which is the master database of suppliers. These can be categorized and sub categorized through drop-down lists. I've done this mainly using the =INDIRECT("x") function in Data Validation and seems OK.

    Now the challenge:
    I'm looking to make another table to select only a few of suppliers for a given event from the Master List, navigating & then filtering by sub-category.
    If we can get this working, I'd want the selected suppliers details to automatically fill in to the selected supplier area.

    Attached is an example spreadsheet, partially working.
    The idea is that you'd make a selection in cell P2, eg.DJ. Cell Q2 would then have a second drop-down showing all the suppliers with 'DJ' as their sub-category in the Master Supplier List.

    The coloured area is the part I'm really struggling with.
    I've watched a load of tutorials and not really sure how to progress.

    Any advice would be great!
    Thanks, Mic

    edit
    Adding image for quick reference.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by Mic_ExcelNoob; 07-26-2020 at 07:15 PM.

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Dependent Dropdowns & Auto Filling Cells based on table

    A couple of ideas for you.
    There are no on-sheet formula involved - both are macro driven forms
    The simple one only requires the headings of the services changing to suit.
    And the word "YES" placing in the intercecting cell (i.e. supplier name(row) service(column))
    torachan.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-26-2020
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    6

    Re: Dependent Dropdowns & Auto Filling Cells based on table

    Thanks for the idea torachan.
    I've never used anything like those macro-driven forms and they might do the job.

    In the example with the regions & trades (categories/subcategories), it might bring up a list of a few sub-contractors that meet the search criteria.
    If you select one, is there a way to get that selection back into the excel document and populate cells such as name, contact info etc?
    Attached Images Attached Images

  4. #4
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Dependent Dropdowns & Auto Filling Cells based on table

    The data already exists on Sheet1 - the code uses a series of filtered tables to arrive at a result.
    If you scroll the sheet to AO:AR the resulting table that populates the listbox is there.
    Alternatively add a sheet and leave its default as Sheet2 and add/paste the code into the CommandButton1-Click event right near the end.
    This will pass the data to Sheet2 starting at A1.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    07-26-2020
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    6

    Re: Dependent Dropdowns & Auto Filling Cells based on table

    Hi torachan,

    Thanks again for this.
    I've been having a look and I can see where the selection is being created in the sheet - this thing is amazing!
    I've tried adding a whole other category into the sub-contractor list and the tool picks it up no problem so this looks like it is going to do what I need.

    Only 2 questions:

    1. If I'm using the form to select a contractor, the form can narrow it down to a list of three options for example. All three of those options are displayed in cell AO-AR. But selecting a single option in the form doesn't seem to narrow the data on sheet in any way.
    See image attached.
    I've selected Sub-67 in the form but still three options in the sheet.
    Sorry if I'm missing something obvious.

    2. Assuming the above works and we can select a single sub-contractor, can you select multiple? Eg, I want to book a bricklayer and a carpenter and define which ones.

    Thanks again for the help, this is looking pretty promising - although I have no idea how it's working
    Attached Images Attached Images

  6. #6
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Dependent Dropdowns & Auto Filling Cells based on table

    The selected contractors listbox is now multichoice.
    On pressing new blue button the choices are transferred to column 'AX'
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-26-2020
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    6

    Re: Dependent Dropdowns & Auto Filling Cells based on table

    Hi Torachan,

    It still doesn't seem to be fully functioning.
    I can't seem to narrow down to a single contractor. Any ideas?

    Also, I'm thinking it would be useful to try and understand how this whole thing works to be able to customize in future.
    Should I be exploring VBA?
    Any suggestions on where to start?

    Thanks again

  8. #8
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Dependent Dropdowns & Auto Filling Cells based on table

    I have just downloaded the file from the site and ran it.
    It does work as described - placing your single choice at column 'AX'
    To understand the cascading filtering a knowledge of VBA is useful.
    I have attached the code file for referencing - step through it bit by bit - the flow will become understandable with a bit of web searching.
    torachan.Capture1.JPGCapture2.JPG
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-26-2020
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    6

    Re: Dependent Dropdowns & Auto Filling Cells based on table

    OK great.
    Thanks once again.
    My VBA journey starts now!

    Thanks for all the help and advice Torachan

  10. #10
    Registered User
    Join Date
    07-26-2020
    Location
    Australia
    MS-Off Ver
    Office 16
    Posts
    6

    Re: Dependent Dropdowns & Auto Filling Cells based on table

    FYI - When opening previously I never had the 'Transfer Choices to Sheet1("AX") button for some reason.
    I just deleted previous versions and re-downloaded and works fine.

    Thanks again!

+ 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. SumIfs function based on multiple dependent Dropdowns
    By mjali001 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-23-2020, 09:19 PM
  2. Replies: 6
    Last Post: 03-03-2017, 02:45 AM
  3. Auto filling in cells based on other column drop down selections
    By mhewitson15 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-21-2016, 11:16 AM
  4. Clear Contents of Dependent DropDowns in a range but keep formulas for those cells
    By jeffclanders in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-22-2015, 10:51 AM
  5. Auto filling a table based upon another table
    By zxcvb12 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-08-2011, 02:06 PM
  6. Auto filling cells based on drop down menu selection
    By Candace84 in forum Excel General
    Replies: 1
    Last Post: 11-19-2010, 02:59 AM
  7. Replies: 3
    Last Post: 11-04-2010, 10:00 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