+ Reply to Thread
Results 1 to 10 of 10

Data Validation Lists

  1. #1
    Registered User
    Join Date
    01-28-2014
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    50

    Data Validation Lists

    Hi - just wondered if anyone can help me with filtering list information.

    I've got a worksheet that will show a place in column B (from a Data Validation list - list is in Sheet 2), and then I want to filter column C to only list asset ID's that belong to that place (all assets are listed in sheet 2 with the places), for example, if I select Albertson Avenue as my place name, in column C, the list that would show would be all assets belonging to Albertson Avenue.

    Is this possible using a formula? I've attached a copy of my spreadsheet for your information - please note that the data in sheet 2 is a lot less than there should be as I had to reduce this to reduce the file size!

    Thanks!
    Emma
    Attached Files Attached Files

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

    Re: Data Validation Lists

    If I understand correctly then remove the data validation from sheet2 column C, select cell C3 and paste the following array entered formula* into the formula bar:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    *Activate the formula by simultaneously pressing the Ctrl, Shift and Enter keys before you copy the formula down the column.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Data Validation Lists

    try here: Dependent Drop Down Lists
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  4. #4
    Registered User
    Join Date
    01-28-2014
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Data Validation Lists

    Thanks JeteMc - That almost worked! the only thing is it just gives me one asset record back in column c when I select the place, however some places have 30 assets that we'd want to be able to choose from, if that makes sense!

    And thanks for link sandy666 - will have a look through that now

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    12,939

    Re: Data Validation Lists

    I am going to guess that you pasted the formula into the formula bar and pressed enter instead of simultaneously pressing Ctrl, Shift and Enter. If you use the CSE activation, before copying the formula to the other cells in the column, and then select Albertson Avenue in B3 you should get 21 Asset ID's.
    Let us know if you have any questions.

  6. #6
    Registered User
    Join Date
    01-28-2014
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    50

    Re: Data Validation Lists

    Hi JeteMc - thanks for the additional post - I got that working now The only issue is, I'd like to see the list of all the Asset IDs in a drop down list, rather than copying and pasting the formula down the list to see all the assets. For example, if I selected Albertson Avenue in cell B3, in cell C3 I get a drop down list of all the asset IDs at Albertson Avenue - I've added a word document with a screenshot of what I'd like to see once I've selected a place in cell B3 if that helps!

    Thanks for all your help with this, I really appreciate it! :D
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    12,939

    Re: Data Validation Lists

    Those are called dependent drop down lists, as Sandy's post states. If you look at the link Sandy provided you'll see that you will need to make a named list for each unique location in the UNITDESC column, i.e. 'Albany_Street' would have the following UNITID's in the list: ALBA01-01AMTUR01, ALBA01-10PLANT01, ALBA01-10PLANT02, ALBA01-15BEDDG01, ALBA01-58SIGNS01
    Let us know if you have any questions.

  8. #8
    Registered User
    Join Date
    01-28-2014
    Location
    Dunedin, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    50

    Post Re: Data Validation Lists

    Bother Was kind of hoping that wasn't the only way.....there's over 400 places, so over 400 named lists!! Thanks so much for your help though, and great to know that new formula as well

    Cheers
    Emma

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    12,939

    Re: Data Validation Lists

    You are talking about 400 unique locations, correct? I ask because I noticed that in the file attached to post #1 there were three unique locations, however two of them were listed multiple times in the drop down.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    12,939

    Re: Data Validation Lists

    This might be of some use. On sheet 3 a pivot table is connected to two slicers. The first slicer lists the unique locations and once a location is selected the Unit ID's associated with that location move to the top of the second slicer. Also the Unit ID's for the other locations are grayed out.
    Let us know if you have any questions.

+ 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. Data Validation Combo Box with Multiple Independent Validation Lists
    By firstofnine in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-24-2016, 04:38 PM
  2. [SOLVED] Three Data Validation Lists: Third List works from first two Lists BUT...
    By em1335 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-07-2016, 12:46 AM
  3. Replies: 3
    Last Post: 06-04-2015, 02:27 PM
  4. [SOLVED] data validation lists - drop down lists too small
    By reglook0736 in forum Excel General
    Replies: 3
    Last Post: 04-24-2015, 12:49 PM
  5. Data validation depending on other data validation without named lists
    By Stormbringer in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-20-2015, 11:24 AM
  6. Replies: 3
    Last Post: 09-02-2010, 03:04 AM
  7. data validation lists
    By -Brian-H- in forum Excel General
    Replies: 4
    Last Post: 09-09-2008, 09:49 AM

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