Thread: Confused about drop down lists.

Ok i am new to Excel and to these forums but here i go.

Instead of trying to describe what i need i will just show it. my fire is still a massive work in progress but what it depicts is.

I am making a spread sheet from a list of information me and some friends are compiling for a game we play.

As you can see,
The A Column is the slot the item goes in.
The B Column is the items name.
The C to X columns is the information on the item. still being expanded on.

What i am trying to figure out is. I need to add drop down lists in the B Column for each slot so i can add new items to the list. when i select each new item in the list it needs to update the stats in the C > X column. So in essence if i say have 3 items in the B2 cell. if i change from item 1 to item 2. all the stats in cell C2 > X2 need to change to what item 2 has listed. then back again when i change back.

The chart still needs to be readable with the =SUM command i have in the totals line. thats why i don't have say. each slot on its own page with each item listed per column.

The drop down menu for the B Cells needs to be able to be updated with ease and the chart needs to be kept clean. most likely i will need to add more sheets foe each slot or something.

I just do not know how to make a drop down list populate all those cells with the saved information. still being new to excel (have been playing at it for less than a week now) i don't know all the advanced commands so forgive me if there is a big button on the program that shows how to do this.

Figured it would be better to ask those who have been using this longer than me so i can do this right. rather than messing with it and ruining my work.

So to sum it up. i need to add a drop down menu in each cell in the B column. each drop down needs to have easy access to add new items and take old ones out. and it needs to show the entered information correctly in the C to X columns.

Hope this is doable.

Hello,

you'll need to build a list of all the possible names and their respective item values on a separate sheet.

Then you can define range names for lists of names and use these range names as the source for list data validation.

Finally use a Vlookup formula to look up the item values from the other sheet.

I've applied the above to rows 2 and 3 of your sheet. If you select a different value from the drop-down, you will see the values in columns C to X change.

cheers

Teylyn nailed that. The only additional thing I offer is to make the lookup table dynamic so that it expands itself as you add rows to your database. So in this workbook, you can add to the Database sheet without having to fiddle with the named range for that data.

I also changed the name of the sheet to Database since that's what it is, and Lookups was dangerously close to the function LOOKUP() which can lead to daunting problems, best not to name sheets too closely to function names.

Hey thanks alot. that worked. question though. is there a faster way to copy and change the =Vlookup command from each Cell to the next? or do i need to retype the line each time? and also. is there a way to make the Vlookup command more dynamic so i can add and remove things from the database list at will and not need to change the command?

Say if it have the start of a list at C12. so the COLUMN(C12) is in the line..

Then later i add a new item to the list above it causing C12 to be the end of another list. will i need to change the command line to COLUMN(C13) and all the other commands from there down?

When you copy the formula ACROSS from the first column it is in, the formula in the cell will adjust itself. You just need to get the right value to appear in the first cell properly, then you can copy down and across and it should adjust itself properly.

To make the lookupTable named range adjust as you add more data to the Database sheet, change the formula to:

Insert>Name>Define>lookupTable> RefersTo:
=Lookups!$A$1:OFFSET(INDEX(Lookups!$A:$A, MATCH("zzz",Lookups!$A:$A, 1)),,99)