Multiple Drop Down boxes to give a value

[ian_noobster]

Hi,

I'm trying make have a sheet with drop down boxes which relate to the first 3 columns of the values below and basically make a singular formula that will look at the information from the drop down boxes and give me the code.

I think its something along the lines of

If Drop down 1 = product 1 & Drop down 2 = Blue & Drop down 3 = 10 the result will be Example code 1

Is there way of doing this?

[TABLE="width: 368"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD]Colour[/TD]
[TD]Size[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]Blue[/TD]
[TD]10[/TD]
[TD]Example Code 1[/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]Green[/TD]
[TD]5[/TD]
[TD]Example Code 2[/TD]
[/TR]
</tbody>[/TABLE]


Sorry i haven't explained this very clearly

Cheers

 

[stunnrock]

If you just want to view the data, there is a feature built into Excel called Filter. If you are using a recent version, you'll find it at the right-hand side of the Home ribbon. Just select any cell in your data and then Home > Sort & Filter > Filter. The header row will get drop-down boxes, that do just what you describe.

If you need this to appear somewhere else in your workbook, you can use an Advanced Filter (found under the Data menu).

It is even possible to do this with a combination of Data Validation and cell-formulae, but that would take a lot more work to set up.

 

[bvbull200]

Hi,

I'm trying make have a sheet with drop down boxes which relate to the first 3 columns of the values below and basically make a singular formula that will look at the information from the drop down boxes and give me the code.

I think its something along the lines of

If Drop down 1 = product 1 & Drop down 2 = Blue & Drop down 3 = 10 the result will be Example code 1

Is there way of doing this?

[TABLE="width: 368"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Colour[/TD]
[TD]Size[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]Blue[/TD]
[TD]10[/TD]
[TD]Example Code 1[/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]Green[/TD]
[TD]5[/TD]
[TD]Example Code 2[/TD]
[/TR]
</tbody>[/TABLE]


Sorry i haven't explained this very clearly

Cheers

Are you wanting the value under the "Code" heading to change based on the combination of values selected in the first three columns?

That could probably be solved with a string of IF statements. Maybe not the most elegant solution to others, but what I would do is create a table of all valid combinations on a separate sheet, have a cell concatenate each of the values selected in the first three columns, then put a string of IF statements in the "Code" column that displays the desired Example Code number.