+ Reply to Thread
Results 1 to 8 of 8

Populate cell data based on data validation (drop down list)

  1. #1
    Registered User
    Join Date
    01-29-2014
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    4

    Populate cell data based on data validation (drop down list)

    Hi everyone,

    I have 2 work sheets, the first being the main sheet using drop down lists based on data in the second work sheet. I was wondering how I would be able to populate other cells based on what is chosen in the drop down list. The option chosen in the drop down correspond to some data found in the other worksheet (these are the values I want to auto-populate the other cells)

    I have shown in the 1.jpg the values I would like to extract from the second work sheet (2.jpg). Ideally by the end of this I would like to be able to have my first worksheet auto populate based on the drop down list.

    I was wondering if the community had any ideas on how to achieve this?

    Thanks.
    Attached Images Attached Images

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

    Re: Populate cell data based on data validation (drop down list)

    It would be easier for us to understand if we could see the data in a file.

    Can you post a small file?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    01-29-2014
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Populate cell data based on data validation (drop down list)

    Of course, hope this clears things up.
    Attached Files Attached Files

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

    Re: Populate cell data based on data validation (drop down list)

    Try this...

    First, unmerge the cells A15:B15 downward.

    Enter this array formula** in A15:

    =IFERROR(INDEX('Sheet 2'!C$3:J$22,MATCH(A$14,'Sheet 2'!A$3:A$22,0),SMALL(IF(ISTEXT(INDEX('Sheet 2'!C$3:J$22,MATCH(A$14,'Sheet 2'!A$3:A$22,0),0)),COLUMN('Sheet 2'!C$3:J$22)-COLUMN('Sheet 2'!C$3)+1),ROWS(A$15:A15))),"")

    Copy down until you get blanks.

    Enter this array formula** in E15:

    =IFERROR(INDEX('Sheet 2'!C$3:J$22,MATCH(A$14,'Sheet 2'!A$3:A$22,0),SMALL(IF(ISNUMBER(INDEX('Sheet 2'!C$3:J$22,MATCH(A$14,'Sheet 2'!A$3:A$22,0),0)),COLUMN('Sheet 2'!C$3:J$22)-COLUMN('Sheet 2'!C$3)+1),ROWS(E$15:E15))),"")

    Copy down until you get blanks.

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  5. #5
    Registered User
    Join Date
    01-29-2014
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Populate cell data based on data validation (drop down list)

    Thanks for the help but I kind of cleaned up my spreadsheet to make it easier for myself and others to understand. I tried to use the above formula but it didnt quite work. I was wondering what the best way to approach this would be.

    In my 'Blend Sheet' the cell highlighted red is a drop down list. Based on what is chosen I want to fill the subsequent rows. The cells highlighted in blue are the component name which I want to extract ONLY if there is a corresponding value for it (green column). And that value will appear in the cells highlighted green in the 'Blend Sheet'.

    I began to use a VLOOKUP function but quickly realized it would be verry difficult to do.
    Any help or suggestions would be greatly appreciated!
    Attached Files Attached Files

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

    Re: Populate cell data based on data validation (drop down list)

    Can you redesign the Blend Types sheet?

    Put the blend types across row 1. Put the components/values down the columns. Like this...

    Data Range
    A
    B
    C
    D
    E
    F
    1
    A
    B
    C
    D
    E
    2
    1
    498
    500
    3
    2
    590
    500
    4
    3
    330
    5
    4
    6
    5
    50
    7
    6
    500
    8
    7
    20
    9
    -----
    -----
    -----
    -----
    -----
    -----


    It would make things a lot easier.

  7. #7
    Registered User
    Join Date
    01-29-2014
    Location
    canada
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Populate cell data based on data validation (drop down list)

    I've rearranged the blend types but still wondering how to approach this.
    Attached Files Attached Files

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

    Re: Populate cell data based on data validation (drop down list)

    Unmerge the cells in B15:C15 downwards. Merged cells are a "cancer" in your file! All they do is lead to problems!

    Enter this array formula** in B15:

    =IFERROR(INDEX('Blend Types'!A:A,SMALL(IF(INDEX('Blend Types'!B$2:W$8,0,MATCH(B$14,'Blend Types'!B$1:W$1,0))<>"",ROW('Blend Types'!B$2:W$8)),ROWS(B$15:B15))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down until you get blanks.

    Unmerge the cells in F15:G15 downwards.

    Enter this formula in F15:

    =IF(B15="","",VLOOKUP(B15,'Blend Types'!A$1:W$8,MATCH(B$14,'Blend Types'!A$1:W$1,0),0))

    Copy down until you get blanks.

    Here's your file with this implemented.

    ish_baho(2).xlsx

+ 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. Replies: 7
    Last Post: 09-11-2013, 07:35 PM
  2. Populate data in cell based on drop down list selection
    By dwoodson297 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-14-2013, 02:07 PM
  3. Macro/VBA to populate information based on date selected from data validation list
    By anonDymous in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-12-2013, 11:03 AM
  4. [SOLVED] Excel 2007 Macros based off Data Validation list to populate rows
    By nawilliams62 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-19-2012, 02:53 PM
  5. Lookup Value based off Data Validation List / Populate Form
    By snake10 in forum Excel - New Users/Basics
    Replies: 13
    Last Post: 03-22-2009, 02:57 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