Hi All,

Thanks for anyone who can help me, basically I have a spreadsheet I am looking at creating that has heaps of rows with different chemicals, and across the columns is different samples and down the column is the result.

What I want to do is apply a set criteria to each row across each sample and there are four criteria:

Criteria 1: lower than value W,
Criteria 2: lower than X but greater than W,
Criteria 3: lower than Y but greater than X,
Criteria 4: greater than Y,

Basically these values will be different for each row, that is different chemicals but each sample (column) will have the same conditions, and then what I want to be able to do is color the cell depending on the criteria, and then depending on each of the cells in the column apply the greatest letter criteria, so if I have twenty columns, and each row has a different result, and say column 1 has a criteria 1 result, four criteria three results and one criteria four result, the highest criteria result becomes the overall result at the bottom of the spreadsheet, so in this case it would be criteria four.

I thought about conditional formatting but because the columns might have 1 or 100 it is not really readable, I'd love to have one spreadsheet I could cut and paste results in and it just works.

Des anyone have any idea on how I could achieve this?