Drop down list fills out table for data entry guidelines

kylewestplays

New Member
Joined
May 18, 2019
Messages
1
Hello!

I hope I have come to the right place. I have been searching for the past several days for a solution to my conundrum and I haven't been able to find anything.

The company that I work for is...significantly behind the times in regards to data entry and computer use. I have recently been redirected to the quality department, where quality tests are done on the products that we produce and the tests are measured against certain standards set forth by the higher ups in the quality department. That said, all of the calculations and data entry is done by hand using paper and pencil, and I have taken it upon myself to come up with an easier, efficient, and effective means of documenting these tests.

I have designed an Excel worksheet for the data entry. The really basic stuff, like many of the calculations that we were once using calculators for, I was able to modernize with no problem. Truth be told, the worksheet I have created is already vastly better than the original method, but I want to pull out all the stops before I go to the heads of the quality department with my redesign.

Here is a picture of the worksheet.
https://imgur.com/a/kzOHs3U

What I would like to accomplish next is having the Limits table change to reflect the product being tested when the product is selected from the Code #: drop down list.

So, for example, Product A is selected from the Code #: drop down list, all the cells from
A10 to Z14 are changed to reflect the quality standards for Product A. Rinse and repeat for Product B through D.

I've done some research, and I've had some minimal success. there appear to be different methods for achieving what I want to get done. One method involved Index and VLookup, another method involving Power Queries.

My knowledge of Excel, Excel functions, and Excel formulas are very basic, but I have a mind for programming and I'm a quick learner. I'm not here to ask someone to do the work for me, but if someone could provide an example of how I can achieve what I want done that I can look at and figure out what's going on, that would help out tremendously.

If it helps, I've already created a table containing the information for each product on a second sheet, but where I'm running into a snag is how to translate that information onto the main sheet when the product code is selected from the drop down menu.

Thanks a bunch in advance for taking to time to understand my problem!
kzOHs3U
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
I do not know how your data is on the other sheet, therefore I do not know how to search and what data to obtain.
But let's start with this:

=SUMPRODUCT((Sheet2!$B$1:$D$1=$A11)*(Sheet2!$A$2:$A$4=$C$3)*(Sheet2!$B$2:$D$4))
=INDEX(Sheet2!$A$1:$E$4,MATCH($C$3,Sheet2!$A$1:$A$4,0),MATCH($A12,Sheet2!$A$1:$E$1,0))

The above formulas perform, in a different way, a search and obtain the same result.

I attach the file so you can see how they work. Check the color details in the formula.

https://www.dropbox.com/s/3vlrq10x45452fk/Drop down fills.xlsx?dl=0
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top