Conditional formatting cells with lowest value per row excluding specific columns

Question

I've looked around but haven't been able to find an exact answer to this question without learning everything there is to know about excel formulas.

I'm trying to use one conditional formatting rule to highlight one specific cell in each row in a dataset.

I've got that working with this: =AND(D5<>0,D5<=SMALL(IF($D5:$W5<>0,$D5:$W5),1))

The next part is having the formula exclude hidden columns/columns that have the header "USA" for that I have this formula: =SEARCH("US",D3)=1

Which works to highlight the cells with USA written in them.

I need to combine these two formulas to have it highlight the single lowest value in each visible row.

---

On a separate note I then want the highlighted value displayed at the end of the data set. This way if I update the document with a lower price it will automatically update the rest of the sheet with this information.

Answer 1

I am not sure of your ultimate objective (eg mention of hidden columns/columns that have the header "USA" - is that one condition or two? - and each visible row - is that because part of each row is hidden or are rows hidden as well as columns?) but your immediate requirement seems to be to combine two formulae into one for CF and for that the following may suit, assuming from D5 to Wn is selected first:

=AND(D$3<>"USA",D5<>0,D5<=SMALL(IF($D5:$W5<>0,$D5:$W5),1))