+ Reply to Thread
Results 1 to 8 of 8

Drop Down Box, return multiple Rows with multiple Columns - No VBA

  1. #1
    Registered User
    Join Date
    12-17-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    12

    Drop Down Box, return multiple Rows with multiple Columns - No VBA

    I really feel like I'm missing something obvious, but I'm stuck ...

    The basic idea is I'm wanting to produce the functionality of the AutoFilter - you select one of several choices of products and in the area below, all rows from the "masterlist" that match that product number are listed along with the associated 8 cells of data.

    I don't want to use any VBA because the network security here is so tight I can't edit or implement most of those types of things.

    I know the easy solutions is "just use autofilter", but the purpose of this spreadsheet is to have only a simplified interface for the end user.

    I've been experimenting with VLOOKUP - I've used it before. And either it's not what I need to do, or if it is - I'm screwing something up.

    What's the most efficient way to go about doing this?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,018

    Re: Drop Down Box, return multiple Rows with multiple Columns - No VBA

    Hi and welcome to the forum

    Have you tried using the regular filter? Its under Home/editing (at the end) - select sort and filter. All the use does is click on the pull-down, select what they want...and bingo
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-17-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Drop Down Box, return multiple Rows with multiple Columns - No VBA

    Yes, that's what I'm calling the "auto-filter", sorry if that's not the correct name.

    I don't want the entire list of data visible. I want the list hidden away from sight with only 1 available drop down box.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Drop Down Box, return multiple Rows with multiple Columns - No VBA

    Can you post a SMALL sample file that shows how your data is setup?

    I may not be able to get to it tonight (almost my bed time!) but I can get to it tomorrow. This is usually not too difficult but if you have 1000's of rows of data it may take a few seconds to calculate.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    12-17-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Drop Down Box, return multiple Rows with multiple Columns - No VBA

    here's a small "sample". i can't really include actual data due to privacy agreements I'm bound to ... but you should get the picture. these are the actual columns of data. there are multiple products being tested and each of them may have different tests being performed - ranging from 3 to 12 possible analyticals

    when you turn the filter on, click drop down and select say "purple ooze", it will show all the rows that have purple ooze as the product.

    what i'm hoping for is to set up a page that would when selecting the appropriate product from the drop down, the area underneath would appear something like ...


    product name
    material #
    Common name

    test1 target value max min
    test2 target value max min
    test3 target value max min
    ...
    up to however many tests are there.

    I would then set up my print area so that the end user will select the product, and click print and see a quick snapshot of the tests that will need to be performed followed by the criteria.


    i realize the ease of using the filter, but i need to (believe it or not) simplify it further, plus printing something that looks like a spreadsheet would complicate it further



    thanks for any advice
    Attached Files Attached Files

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Drop Down Box, return multiple Rows with multiple Columns - No VBA

    OK, here's your file:

    Sample(1).xlsx

    The formula entered in A10 is to be copied across for the max number of columns of data for any product and copied down for the max number of rows of data for any product.

  7. #7
    Registered User
    Join Date
    12-17-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Drop Down Box, return multiple Rows with multiple Columns - No VBA

    That's EXACTLY what I was looking for ... INDEX and MATCH, funny I've never come across them before, but it seems like I really should have!

    Thanks a bunch!

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Drop Down Box, return multiple Rows with multiple Columns - No VBA

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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