Automatic Population

**mediumquality** · 03-25-2012, 11:31 PM

Hello!

I'm a department manager at a manufacturing plant and I've been working on an hourly performance management spreadsheet to track operator production versus standards. Each product has a different run speed (i.e. Cereal Box @ 20,000 units per hour). I've gone so far as to list each product and create a drop down list using the data validation feature. My goal is to automatically populate the standard speed cell based on what product is selected from the drop down list. Any ideas?

Thanks!

**DGagnon** · 03-25-2012, 11:37 PM

you can do this with a vlookup, something like this:

=VLOOKUP(A1,Sheet2!A:B,2,0)

where A1 is your dropdown list
Sheet 2 range A:B has a list of yoru products (Drop Downs) and units per hour
2 means after you find A1 in the range, look at the second column of the range and return that
0 means find an exact match.

**mediumquality** · 03-25-2012, 11:53 PM

That worked great! Is there any way to not show #N/A when the drop down field is blank?

**DGagnon** · 03-25-2012, 11:55 PM

Sure is, give this a shot

=IFERROR(VLOOKUP(A1,Sheet2!A:B,2,0),"")

**mediumquality** · 03-25-2012, 11:58 PM

Wonderful! Now for some aesthetics...I would like for their actual speed to show up in green if it exceeds the standard speed and in red if it doesn't. How could I go about doing that?

**DGagnon** · 03-26-2012, 12:02 AM

you can do that with conditional formating, just select the column you want to compare add a new conditional format select formula and use something like this

=$A1>$B1

or something along those lines depending where and which way you want to go.