having a table in excel

[Guest]

I was wondering how I can have a drop down list in a cell that has about 100
items in the drop down, and when you select the drop down item you want it
fills in all of the other columns relating to that item.

For example, on this example there would be 100 other items but when I am in
column 1 I want a drop down list and have it pull in column 2 and 3.


Item Description Price

A Testing Item 5.00

Thanks a bunch

 

[Conrad Carlberg]

Hi ashaback,

I'm far from sure that I'm clear on your example, but here's my
understanding:

You have a dropdown in Column A, probably by means of Data Validation based
on a list, that offers the user the choice of about 100 items. When the user
chooses "A" from that dropdown, you want Column B -- in the same row -- to
display "Testing Item" and Column C to display 5.00.

The usual way to do that is by means of a lookup table, particularly when
you have so many choices for the user. In the case you describe, the lookup
table would contain 3 columns and about 100 rows, one row for each item in
your dropdown. The first column in the lookup table would contain the same
values as the list on which you base the dropdown. The second column would
contain the Description of the item that's in the same row of the lookup
table. And the third column would contain the Price value.

Suppose your lookup table is in L1:N100. You use a dropdown in A1 to select
a value into A1. Now, this formula in B1 could return "Testing Item":

=VLOOKUP(A1,$L$1:$N$100,2,0)

and this formula in C1 could return 5.00:

= VLOOKUP(A1,$L$1:$N$100,3,0)

The first argument to the VLOOKUP function, in this example A1, tells Excel
what item to look for in the lookup table's first column. The second
argument tells Excel where to find the lookup table; I use dollar signs to
anchor the range so you can copy the formula without altering the lookup
range address. The third argument (2, then 3) tells Excel which column in
the table to return. And the fourth argument, in this case 0, tells Excel to
find an exact match between A1 and values in column L, in which case you
needn't worry about how they're sorted.

 

[RagDyer]

Check out this web site of Debra Dalgleish.

Scroll down to the "D's", and see all the pages on "Data Validation", which
is what you can use to do what you're looking for, including bringing up
data in related columns.

http://www.contextures.com/tiptech.html

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================