Home |
Search |
Today's Posts |
#1
|
|||
|
|||
having a table in excel
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 |
#2
|
|||
|
|||
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. -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 "ashaback" wrote in message ... 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 |
#3
|
|||
|
|||
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! ============================================== "ashaback" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Table copied from Excel to powerpoint print all bold? | Excel Worksheet Functions | |||
Use Excel to make an update query to another database table? | Excel Discussion (Misc queries) | |||
Excel Pivot Table with Access | Excel Discussion (Misc queries) | |||
Pasting a Word Table into Excel | Excel Discussion (Misc queries) | |||
How do I get a table from IE into excel? | Excel Worksheet Functions |