+ Reply to Thread
Results 1 to 5 of 5

Populate cells based on drop downs and quantity

  1. #1
    Registered User
    Join Date
    08-14-2015
    Location
    Boston
    MS-Off Ver
    2010
    Posts
    2

    Populate cells based on drop downs and quantity

    Hi everyone,

    First time poster here and I have limited excel experience. After scouring dozens of youtube videos i cant find what im looking for.

    I am trying to create an easy way to populate cells based on 2 drop down selection and quantities.

    In the example I uploaded I would like the 'Product' drop down and
    Term' drop down combined with the Quantity to populate the Part, Detail, and MSRP fields.

    I have a few hundred lines of products i want to be able to quick look up. Once I have an idea of how to do it I can probably expand it to encompass all.

    Thank you for any help or guidance.
    Attached Files Attached Files

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Populate cells based on drop downs and quantity

    Highlight the cells in column B you require for inputs

    Select Data > Data Validation > Data Validation

    Enter List into the Allow: box

    Enter Hardware,Software in the Source box.

    Click OK

    Repeat for column C, using 1,2,3,4,5 in the source box

    In cell D4 enter this formula

    =INDEX(L$4:L$13,MATCH($B4&$C4,$J$4:$J$13&$K$4:$K$13,0))

    and confirm with CTRL, SHIFT and ENTER


    copy the formula across and down as required.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    08-14-2015
    Location
    Boston
    MS-Off Ver
    2010
    Posts
    2

    Re: Populate cells based on drop downs and quantity

    HI Sweep,

    Thank you for the quick response.

    I am receiving a #VALUE!error in D4 when I input the =INDEX(L$4:L$13,MATCH($B4&$C4,$J$4:$J$13&$K$4:$K$13,0))

    Could I be entering the drop down data incorrectly? B4 I have List (ignore blank checked, in cell dropdown checked) Source =$J$4:$J$13 and =$K$4:$K$8 in the term drop down data.

    I have uploaded the new worksheet with my work.
    Attached Files Attached Files
    Last edited by avozella; 08-14-2015 at 10:30 AM.

  4. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Populate cells based on drop downs and quantity

    I am receiving a #VALUE!error in D4 when I input th
    Your error is occurring because the formula hasn't been confirmed with CTRL, SHIFT and enter. When correctly entered, the formula will display within curly {} brackets. Don't manually enter these, it won't work.

    Could I be entering the drop down data incorrectly? B4 I have List (ignore blank checked, in cell dropdown checked) Source =$J$4:$J$13 and =$K$4:$K$8 in the term drop down data
    Not incorrect, but if you go about it as described above

    Select Data > Data Validation > Data Validation

    Enter List into the Allow: box

    Enter Hardware,Software in the Source box.
    It will avoid a lot of repetition in the drop down list.

  5. #5
    Registered User
    Join Date
    08-12-2015
    Location
    Malaysia
    MS-Off Ver
    2013
    Posts
    3

    Re: Populate cells based on drop downs and quantity

    Hi Guys,

    Please see my attachment before you read this questions;
    test.xlsx
    When I select the project code from drop down list in sheet 1 "Production hours" in the 2nd sheet "Project code" the time should be update automatically

    Is this possible in excel

    Please give me your valuable solutions and suggestions

    Thanks in advance
    Eliltan

+ 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. Macro to populate the tables from Pivots, when values are selected from drop downs.
    By pavan5183 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-08-2013, 10:42 AM
  2. Replies: 0
    Last Post: 04-11-2013, 10:44 AM
  3. Replies: 17
    Last Post: 02-05-2013, 08:20 PM
  4. Replies: 3
    Last Post: 01-31-2013, 06:22 PM
  5. Populate cells based on the selection from a drop down box
    By bartow in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-13-2011, 03:27 PM
  6. Auto-update initial drop-down selection based on previous drop-downs
    By thornomad in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-16-2010, 09:55 AM
  7. Use Drop Downs to Populate Other Drop Downs
    By fbombs in forum Excel General
    Replies: 2
    Last Post: 09-23-2009, 03:17 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