Populating a cell in Excel based on another cell's information

[TOTCOM11]

OK, let me do my best here to describe what I'm trying to do. I understand VBA coding in Access but not much in Excel. For what I'm trying to do on a whole Excel is a better venue for me to use. I have two worksheets in Excel and they are labeled as such:

"Employee Evaluation Form"
"Product List"

The worksheet "Product List" is a list of a bunch of products that I want to evaluate employees on. Each product as a unique ID. Each record in the product list has an ID #, Product Description, and Defect.

I have a cell in the form "Employee Evaluation Form" which happens to be AG3 which I want to be able to type in a Product ID # and load the Product description into cell S3.

For example, if I type "1.1" into my lookup cell (AG3), I want the description "Tenderloin" to pop up in cell S3. On the product list, Tenderloin's record information is in row 3 starting with the ID in column A, Description in column B, and Defect in Column C. So I want Excel to find cell A3, and load cell B3 into cell S3 on the Employee Evaluation Form. In short I'm trying to do a lookup like I would in Access, but I do not know how to do it in Excel.

Can anyone help me?

Thanks,
Chris

 

[Zathras]

You don't need VBA. Look up the function VLOOKUP in the main Excel help file. It was designed to do exactly what you want.

Cell references across sheets can be tricky at first. Use the point and click technique for building up your VLOOKUP formula and Excel will insert the references with the correct format.

I.e., Type =VLOOKUP( and then use the mouse to select the various cells that contain the arguments you need. Click, then type a comma, then click click drag and type a comma, etc.

It may be best to start with a simple test worksheet having a subset of the product list and the input cell all in one sheet, just to get the hang of it.

 

[TOTCOM11]

THANKS, THAT'S JUST WHAT I NEEDED. I COULDN'T FIND IT IN HELP. ALSO, IS THERE A WAY TO MAKE A DROP DOWN BOX THAT SHOWS 2 COLUMNS OF DATA, BUT (OBVIOUSLY) ONLY STORES 1? I KNOW YOU CAN DO THIS IN ACCESS....I'M JUST NOT SURE IF YOU CAN DO IT IN EXCEL OR NOT.

 

[SkipVought]

Hi,

I like to use named ranges


In AG3, use Data/Validation - List - reference your named range for the ProdID

In the adjacent cells
[tt]
AH3: =Index(ProdDesc,Match(AG3,ProdID,0),1)
AHI: =Index(Defect,Match(AG3,ProdID,0),1)

[tt]


Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[sozzer]

For what I'm trying to do on a whole Excel is a better venue for me to use"

You sure?

 

[TOTCOM11]

YES, I AM. YOU'D HAVE TO ACTUALLY SEE WHAT I'M DOING AND THE APPLICATION WHICH I'M USING IT TO UNDERSTAND...