Change values based on value in *combo box*

[ObiWanBaloney]

Hi! I was wondering if there was a reliable way to change the values of other cells based on the drop-down selection of one cell.

For example, in this spreadsheet I am trying to get the "NUMBER" and "LETTER" fields to correspond to the selection of the drop down list (based on column A, or "COLOR"). So if I were to pick "black" from the drop down list, the NUMBER field should return the value "2" and the LETTER field should return the value "b". However, that doesn't seem to be working as it will only return the "FALSE" error unless the NUMBER OR LETTER field is in the same row as the selected COLOR, and I don't really get that.

I have attached the sheet.

[ron2k_1]

F3=

Please Login or Register  to view this content.

F4=

Please Login or Register  to view this content.

EDIT: Edited the column index

[ObiWanBaloney]

Awesome, thank you!

[ObiWanBaloney]

Figured I'd ask in this thread again instead of making an entirely new thread.

This time I'm wondering how I can do the same thing but with a combo box instead of a drop down list. Replacing the old drop down list cell with with either the linked cell (to the combo box) or the combo box itself doesn't seem to work. In fact, I don't think I even did the cell link correctly anyways.

[ron2k_1]

I'd do this:

1. Create a named range so that you don't have to change the input range in the combo box every time you add a new color. This will avoid having all those empty lines in the combobox as well. So, to to Formulas Tab>Name manager>New:Name it "COLORS" (no quotes), and for Refers to, enter this formula: =INDIRECT(ADDRESS(2,1)&":"&ADDRESS(COUNTA(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(30,1))),1))

2. Right click on the combobox>Input Range>COLORS>OK

3. E3 should match equal EXACTLY what is the header in column B (NUMBER). Caps doesn't matter

4. E4 should match equal EXACTLY what is the header in column C (LETTER). Caps doesn't matter

5. Enter this formula in F3: =INDEX($B$2:$C$5,$D$12,MATCH(E3,$B$1:$C$1,0))

6. Drag down formula to F4

Now you'll notice that you can add as many colors in column A, and it will automatically be added to the combobox, without having a big list of unncessary blank entries.

[ObiWanBaloney]

Thanks again Ron! Though I think I'll probably manage better with VLOOKUP function without comboboxes but I'll see what I can do. I might post up a spreadsheet using the real format of the spreadsheet I intend to make instead of one that doesn't directly represent all of what I'm trying to do. It's mostly because I may either have multiple of the same entry in the selectable range (for example, multiple instances of white) or have merged cells, which I think complicates things even more.

Edit: I'm trying to figure out how to do this first to take care of any multiple entries in the same column: http://www.contextures.com/xlDataVal13.html#Top. I know this has to do with named ranges too but I suppose I'll learn the fundamentals of this part first.

[ron2k_1]

If this matter does not relate to the original question. I suggest you start another thread with your new issue. Creating a drop down list from a list containing duplicates is rather easy, but I'd appreciate it you post the question in another thread. Or google it, I'm 100% positive you'll get several hits if you search for "Creating a drop down list from a list containing duplicates".

Kindly mark this thread solved, if you don't have any more queries concerning the original request. As well, kindly make the first post readable. Remember, that this post will help others as well, so change the font to a readable one.

Thanks