+ Reply to Thread
Results 1 to 5 of 5

Data Validation List to Return Variable/Dynamic Data based on Reference Table

  1. #1
    Registered User
    Join Date
    12-16-2010
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    8

    Question Data Validation List to Return Variable/Dynamic Data based on Reference Table

    Hi,

    I'd like to create a table that will return dynamic values for the headers based on a data validation list (drop down list). I've attached a small scaled sample spreadsheet of what I'm trying to do.

    - The first tab (Finished Goods Map) is a matrix that shows a listing of items and the components that make each item; the relationship is confirmed via an "X"

    - The second tab (Tables) is a listing of values based on supplier capacity, (internal) manufacturing capacity and sales demand.

    - The last tab (Item Listing) shows what I'm using now and what I would like to do; there's more detail in the file. The top third of the page shows what I'm doing now, the middle third shows what I would like my information to look like (the presentation) and the last third is just a listing of each component that makes an item and what the headers would look like.

    I've thought about how to do this for a little while, and I cannot for the life of me even think of the formulas to make this work, but I would like to believe that this is possible without needing VBA. I'm also open to alternative ideas too (i.e. If I change how my information is laid out to create a pivot table, would that be better?)

    Thanks!
    Attached Files Attached Files
    Last edited by dilbert1865; 03-13-2014 at 09:08 AM.

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Data Validation List to Return Variable/Dynamic Data based on Reference Table

    Hello Dilbert find attached

    is this what you are looking for ?

    formulas in finished goods map(Third listing) is array formula ! So needed to be confirmed with control shift and enter !

    formula in middle part uses named ranges(to check named ranges go to formulas---> name manager).

    where you wanted to use data validation you haven't mentioned here In the case you wanted to use data validation for items component in the middle part then you can use last table as named range and use indirect formula to refer component
    Attached Files Attached Files
    Last edited by hemesh; 03-13-2014 at 03:45 AM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,492

    Re: Data Validation List to Return Variable/Dynamic Data based on Reference Table

    @ hemesh,
    In Sheet Item listings In B16 use this and drag across.
    Please Login or Register  to view this content.
    SelectA16
    Data validation - Settings
    Allow - List
    Source =$A$25:$A$29
    Last edited by kvsrinivasamurthy; 03-13-2014 at 06:23 AM.

  4. #4
    Registered User
    Join Date
    12-16-2010
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    8

    Re: Data Validation List to Return Variable/Dynamic Data based on Reference Table

    YES! That's it! Thank you so much guys! I'll mark this as solved!

  5. #5
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Data Validation List to Return Variable/Dynamic Data based on Reference Table

    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)

Similar Threads

  1. Data Validation list based on a table that can change
    By KBM in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 02-10-2013, 12:38 PM
  2. Replies: 9
    Last Post: 11-26-2012, 12:46 AM
  3. [SOLVED] Create Dynamic Data Validation List based upon Criteria
    By stubbsj in forum Excel General
    Replies: 6
    Last Post: 06-04-2012, 08:33 AM
  4. Replies: 0
    Last Post: 02-13-2012, 07:18 AM
  5. Replies: 3
    Last Post: 08-21-2011, 08:22 PM

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