Software

Ask something
Discussion Locked

Validation in Excel

0 helpful
5 replies

by jimboom333

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

If I create a validation drop down list for a cell and then use that list to enter a value in the cell, can I create a formula in another cell to reference a valuefor the inserted validation list info?
Example:
My list for cell B16 contains 4 partnumbers:
EB1
EB2
EB3
EB4

EAch of these part numbers has a specific weight
EB1 100w
EB2 75w
EB3 60w
EB4 50w

When I select EB1 from the list for B16, I would like the associated weight (100w) to show up in cell D16.

5 replies

by jimboom333

B16 can only contain the list itself, it cannot include the weight. The weight must be listed elsewhere I suppose and I want it inserted automatically when its corresponding part number is entered in B16.
Entering =B16 will only insert the part number chosen from the drop down list in B16. It will not provide the needed value of the part number.

by dklippert

Sorry, I misunderstood. You can use =VLOOKUP()

Create a list of the weights. Let's say from BA1:CA4. The first column is EBx the second column is the weight. To make it easy, select the two columns and use Insert>Name Define to name it "Weights".

The formula would then be:

=VLOOKUP(B16,Weights,2,FALSE)

This looks up the value of B16 in the Weights Table and returns the corresponding value in the 2nd column. The FALSE entry makes sure that the exact entry is returned, but is probablynot needed in this case

by jimboom333

Thanks. Your answer has helped tremendously. Can you recommend any technical guides for further reading on the subject?
jim.stanker@candoelec.com

This discussion is closed to new replies.