# Populate cells based on drop downs and quantity

1. ## 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.

2. ## 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.

3. ## 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.

4. ## 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. ## Re: Populate cells based on drop downs and quantity

Hi Guys,

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

Eliltan

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

#### 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