Data Validation List: IF only one value THEN cell contains that value, else generate list

I'm trying to work out how to create a data validation list only if there are two or more values to be listed, else the cell should contain the single value. This will streamline my data processing by enabling me to ignore rows with only a single value.

This is related to a previous thread: http://www.excelforum.com/excel-form...umn-title.html The aim of the previous thread was to return the value from the column header (e.g. "Fire") if the value in the row was "1". This was solved by using the formula =iferror(INDEX($B$1:$G$1,MATCH(1,B2:G2,0)),"") to match the first "1" read from the left to its respective column header. This was fine for what I needed.

Now I'd like a data validation list in the column "Type" only when there is more than one "1", else the cell should be the value from the column header.

E.g. For Product A the Type should be listed as "Food", whereas for Product B there should be a list containing "Air" and "Fire". I don't care if there are "0"s in the drop-down list.

Product	Food	Drink	Air	Earth	Fire	Water	Type
A	1	0	0	0	0	0
B	0	0	1	0	1	0
C	0	0	0	1	1	0
D	0	0	1	0	0	0
E	0	0	1	0	0	1
F	0	1	1	0	0	0
G	0	0	0	0	0	0

Thanks for any help.

Ok, this is working perfectly up until the point where I need to generate the data validation list on "Sheet 1" (referencing your worksheet layout).

Did you get your named ranges to fill automatically? e.g. product_a, product_b, product_c, product_d - where the final "_x" is filled automatically by dragging the formula down? I need to be able to do this automatically as I have around 4500 lines of data to apply this process to!

Thanks for any help.

Nick

Ah well, doing it on a single sheet without named ranges worked a treat for me. Thanks for all your help.