Little help please.


Problem:

I am trying to have the ability to have a group of cells change in response to one of them being modified.

Situation:

I have a widget that I want to get prices from different vendors for. I want to note all the different prices and then alert my program which one it is to use.

Setup:

A B C D E F G H
1 Part | Price 1 | Alert | Price 2 | Alert | Price 3 | Alert | Purchase Price
2 Widget | $1.25 | Y | $1.37 | N | $1.02 | N | $1.25



My Approach:

I was attempting to set a data validation for columns C, E, & G using two options, Y or N (Yes or No). Then I wanted to put a formula in each of those columns that would say if one of the other columns was selected with a Y then they would automatically return an N. Then cell H2 would then look for the Y and use the number preceding it.

Note:

I may not necessarily choose the lowest price. I want the user to be able to select which vendor the product was purchased from.